Triggers

--triggers
use automart
Go
--here is simple trigger that does nothing much
--a trigger is always "on" a table
--it must be "for", "after" or "instead of" one or more
--events such as Insert, update or delete
Create trigger tr_doNothing on Person
for insert
As
print 'An insert occurred'
go

--this will fire the trigger
--it also uses unicode (N') to enter non ASCII characters
Insert into Person(LastName, FirstName)
Values(N'Στεφανοσ',N'Κονγεροσ')

Select * From Person

Select VehicleId, Count(VehicleID) From Employee.VehicleService
group by VehicleId
having count (vehicleID) > 1

--Get a count of how many services they have
--if it is > 4
--message that they get a 10% discount
--there are two temp tables you can access
--inserted and deleted
--after happens after the insert has taken place
go

Create trigger tr_FourTimeDiscount on Employee.VehicleService
after Insert
As
--declare internal variables
Declare @vehicleID int
Declare @PersonKey int
Declare @Count int

--get the vehicleId from the temporary
--table inserted
select @VehicleID = VehicleID from Inserted

--get the personkey based on the vehicleID
--because we want to give the discount
--only to registered customers
Select @personkey = PersonKey from Customer.Vehicle
where vehicleId= @VehicleID

--so if the customer exists as a registered customer
if exists
 (Select RegisteredCustomerID 
  From Customer.RegisteredCustomer
  Where PersonKey=@PersonKey)
Begin
--get the count of their visits
Select @Count=count(vs.VehicleID) From Employee.VehicleService vs
inner Join Customer.Vehicle v
on vs.VehicleID=v.VehicleId
Where personkey=@Personkey
--if the count is greater than or = 4
--alert them of the discount
if @count >=4
Begin
--print is not your best method for this
--better return a variable
print 'Congratulations you earned a 10% discount'
End -- end if > than

End --end if exists

--test the trigger
Insert into Employee.VehicleService(VehicleID, LocationID, ServiceDate, ServiceTime)
Values(1, 1,GetDate(), GetDate())
Select * From Employee.VehicleService

go
--this trigger will create a table where 
--a user can see what was deleted
--and have a chance to restore it
--if the delete was wrong or an accident
Create Trigger tr_CustomerDelete on Customer.RegisteredCustomer
Instead of Delete
As
--we check to see if our temp table exists or not
if not Exists
  (Select name from sys.Tables 
   where name = 'RegisteredCustomerDeletes')
Begin
--if it doesn't exist create it
Create table RegisteredCustomerDeletes
(
 RegisteredCustomerID int, 
 Email nvarchar(255), 
 CustomerPassword nvarchar(20), 
 PersonKey int
)
End
--Get values from the Deleted Table
Declare @CustomerId int
Select @customerID = RegisteredCustomerID from Deleted

--do the insert from the deleted table
Insert into RegisteredCustomerDeletes(RegisteredCustomerID, Email, CustomerPassword, PersonKey)
Select RegisteredCustomerID, Email, CustomerPassword, PersonKey from Deleted

--then do the actual deletion
Delete from Customer.RegisteredCustomer
Where RegisteredCustomerID=@CustomerID

--**************end trigger************

Select * From Customer.RegisteredCustomer

--test Trigger
Delete From Customer.RegisteredCustomer
Where RegisteredCustomerID>36

--see if it wrote the deletions
Select * from RegisteredCustomerDeletes