Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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
0
proevo
Asked:
proevo
  • 4
  • 4
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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
 
proevoAuthor Commented:
That is what I needed,
Thanks for all your help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now