Basic Trigger Help

[mossbs]

Hi guys,

I'm no good with triggers so need a little help maybe just a little push in the right direction....(not actually written one before so be gentle with me!!)

Is it possible to create a trigger that would....

Each time a new row is added into the table 'mpUser', if the mpUserName is one of a certain set of names - set the DeactivateExempt column to 1.

Is this possible - i imagine so - just unsure where/how to start!

cheers!

 

[danvlas]

This should be the code within your After Insert trigger.

Update TBL
Set DeactivateExempt = 1
From YourTableName TBL
Inner Join INSERTED INS On TBL.PKField=INS.PKField

PKFied is the primary key of your table.
The trigger will work even in the case of a batch insert, not only for one row.

HTH


Daniel Vlas
Systems Consultant

 

[RiverGuy]

Yes, I would suggest creating a table to store those certain names so that they may be updated over time instead of being hard coded. Let's say you create a table called NamesToExempt.

You could write a trigger on mpUser like this:

CREATE TRIGGER t_mpUser_InsUpd
ON mpUser
FOR INSERT, UPDATE
AS
BEGIN
  UPDATE a
  SET a.DeactivateExempt = 1
  FROM mpUser a
  INNER JOIN INSERTED b
    ON a.PrimaryKeyColumn = b.PrimaryKeyColumn
  INNER JOIN NamesToExempt c
    ON a.mpUserName = c.mpUserName
END

Note that this trigger works on Inserts or Updates. This is in case the name is updated to one of your exemptions.

Also, notice that it updates your mpUser table -- but it is joining the the virtual Inserted table which contains the rows that are inserted or updated. There is also a virtual Deleted table which contains any deleted rows or old values of the updated rows. Those virtual tables are very commonly used in triggers.

Finally, the query joins to the NamesToExempt table to make sure to just update those names you are looking for.

 

[mossbs]

cheers guys!

sterling work - has worked a treat!

cheers

Dan