Simple Insert Trigger Example

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

--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
@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!



Jason Buss

Jason is a senior application developer with Customer FX.

1 Comment

  1. it is very good example of trigger for learn


Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!