Triggers

Use MagazineSubscription
Go
--triggers are procedures that are "triggered"
--by an event, such as
--insert update delete

--this trigger allows the deletion
--of customers without subscriptions
--but records the record in a table
--to be looked at later
Alter trigger tr_CustomerDelete
ON Customer
After Delete
As
--check to see if we need to
--make the table to store the deletions
If not exists
(Select [name] From sys.Tables
Where [Name]='Reminders')
Begin
Create Table Reminders
(
CustID int,
CustLastName varchar(30),
CustFirstName Varchar(25),
CustAddress varchar(100),
CustCity varchar(50),
CustState char(2),
CustZipcode char(11),
CustPhone char(10)
)
End

--insert the deleted record into the
--reminders table
--we can get the record from the temp
--table "Deleted" which exists
--for the duration of the transaction
--a millesecond or so
Insert into Reminders (
CustID,
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Select CustID,
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone
From Deleted


--Just checking

Select * from Subscription

Select * from customer
Delete from Customer
where custID =1

Select * from Reminders

Go
--this stored procedure intercepts
--insertions and checks to see if the
--customer is in Florida
--if he or she is it rejects them with a
--message, otherwise it completes
--the insert
Create Trigger tr_NotFlorida
On Customer
Instead of Insert
As
Declare @state char(2)
--check to see if the state is florida
Select @state=CustState From inserted
if @state='FL'
Begin
PRINT 'We do not serve Florida'
End
Else --if it is not florida
Insert into Customer(CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Select
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone
From Inserted

--test it with florida and
--not florida
Insert into customer(
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Values
('Newman',
'laurie',
'something',
'Tampa',
'FL',
'00887',
'6875551234')

********************************************
Here is a modified trigger for Saunatina and anyone else who is interested.
This trigger uses a cursor to loop through the subscriptions for each customer
in the deletion list and if their subscriptions ended more than 3 years ago
writes them to a subscription history table and deletes them from the subscription table. The customer is not deleted, but is marked for possible deletion in the reminders table. I ran it and it seems to work.

A variation that would make this better would be to loop through subscriptions again after the old subscriptions are removed. If the count of subscriptions for a given customer are 0, then also remove that customer and place him or her in a customer history table. I may try to do this in the near future.

(This whole process probably makes more sense as a stored procedure that is run periodically to clean up the database, rather than as a trigger on Delete.)

Alter trigger tr_CustomerDelete
ON Customer
Instead of Delete
As
--check to see if we need to
--make the table to store the deletions
If not exists
(Select [name] From sys.Tables
Where [Name]='Reminders')
Begin
Create Table Reminders
(
CustID int,
CustLastName varchar(30),
CustFirstName Varchar(25),
CustAddress varchar(100),
CustCity varchar(50),
CustState char(2),
CustZipcode char(11),
CustPhone char(10)
)
End

If not exists
(Select [name] From sys.Tables
Where [Name]='SubscriptionHistory')
Begin
Create Table SubscriptionHistory
(
SubscriptionID int,
CustID int,
MagDetID int,
SubscriptionStart DateTime,
SubscriptionEnd DateTime
)
End
--insert the deleted record into the
--reminders table
--we can get the record from the temp
--table "Deleted" which exists
--for the duration of the transaction
--a millesecond or so
Insert into Reminders (
CustID,
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Select CustID,
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone
From Deleted

--below uses a cursor to loop
--through rows and check on old Subscriptions
declare @CustId int
declare @RowNum int
declare @endDate DateTime
declare CustList cursor for
select CustID from Deleted
OPEN CustList
FETCH NEXT FROM CustList
INTO @CustId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
Select @endDate=SubscriptionEnd from Subscription
Where CustID=@CustID
IF (DateDiff(yy,@endDate, GetDate()) > 3)
Begin
Insert into subscriptionHistory(
SubscriptionID,
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Select
SubscriptionID,
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd
From Subscription
where CustID=@CustID
And SubscriptionEnd = @EndDate

Delete from Subscription where CustID=@CustID
And SubscriptionEnd = @EndDate
End

FETCH NEXT FROM CustList
INTO @CustId
END
CLOSE CustList
DEALLOCATE CustList