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!