I previously posted about using an update trigger to enforce data rules in SalesLogix. Since I was working on another trigger now, I also wanted to post a simple example of an Insert Trigger.
Basically, I have a table called TriggerTest containing a State and also three of Manager fields. When inserting a record for a particular state, I needed to also populate the Manager fields with the current managers defined for each state. I’m using a table called CRMManagers to store a list of each state and the three manager values for that state: (State, Manager1, Manager2, Manager3)
Here’s how I created the trigger:
--First, I create the trigger, naming it trUpdateManagers against the --TriggerTest Table. Create Trigger dbo.trUpdateManagers on dbo.TriggerTest --The trigger is created for Insert, which will fire whenever a row is --inserted to the TriggerTest table. for Insert as
--Now I Declare an ID and State variable and populate them with the new --values inserted into the TriggerTest table. Declare @ID nchar(10) Declare @STATE nvarchar(50) --When setting the ID and State variables, I'm referencing "Inserted" --which is an internal SQL table which basically contains a copy of --the currently inserted row. select @ID = [ID], @STATE = STATE from inserted
--Next I Declare variables for each of the Manager Values Declare @MGR1 nvarchar(50) Declare @MGR2 nvarchar(50) Declare @MGR3 nvarchar(50)
--And now set the Manager variables with the appropreate values --based on the State inserted to TriggerTest Select @MGR1 = [Manager1] , @MGR2 = [Manager2] , @MGR3 = [Manager3]
from dbo.CRMManagers where STATE = @STATE
--Finally, I run an update statement to udpate the newly inserted --TriggerTest record with the Manager values. update dbo.TriggerTest set Manager1 = @MGR1, Manager2 = @MGR2, Manager3 = @MGR3 where ID = @ID
|
That’s about it. Pretty simple!
it is very good example of trigger for learn