Update / Insert Trigger with a twist.

Tigger Needed...

I think that I need a trigger when fired will insert into another table certain amounts of information. I know how to do this; where lies the rub
is that I need to check a column in  the table that this trigger resides on to check for a specific set of values before the insert in to the log table. All the information that I need to check is in the same table that the trigger is on.

IE: if the departmentid is 27 or 36 or 67 insert as normal into the log table; if not skip the trigger altogether.

I am fairly new to triggers so please be gentle...

Thanks
proevoAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that is, because you replace the "INSERTED" by your table name.
change back into FROM INSERTED, and it will only do what you ask for.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot "skip" the trigger, but you can "limit" inside the action based on the "inserted" table contents, like this:


create trigger trg_name
on yourtable
for insert
as
  insert into log_table ( ... )
  select ...
  from inserted
  where departmentid in ( 27, 36, 67 )

0
 
proevoAuthor Commented:
Will this trigger work for updates as well?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that trigger will only be for insert.
now, if you also want an action for update, you might want to either write a second trigger, or try to put all in 1 trigger.
what is the "action" for an update? also an insert, the same way?

then, just add the update in the "for" clause:

create trigger trg_name
on yourtable
for insert, update
as
  insert into log_table ( ... )
  select ...
  from inserted
  where departmentid in ( 27, 36, 67 )
0
 
proevoAuthor Commented:
This is the header information needed to fire a side job that will eventually update a website, this will track any changes on an products table.

Where you mentioned "from inserted" that would be the table that the trigger is on?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Where you mentioned "from inserted" that would be the table that the trigger is on?
no. "from inserted" should be "from inserted" that way, that is the "virtual" table which contains the row(s) that has been inserted/updated

0
 
proevoAuthor Commented:
Also,
When this trigger fires it appears to insert all items in the table instead of the affected row

This is my trigger...
alter trigger MyTrigger
on item
for insert,update
as
  insert into MyTable (ItemID)
  select item.id
  from item
  where departmentid in (28,49,22,54,48,53)

Thanks
0
 
proevoAuthor Commented:
That is what I needed,
Thanks for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.