Hi all, I have a simple need--I ha

[jsteph]

Hi all,
I have a simple need--I have a table into which data will be inserted. There are about 5 fields in this table that are not included in the insert statement (and can't be in the insert stmt for reasons to detailed to get into here).

So, I need a database level trigger in which I need to access data from other table(s) and update those fields. Of course I don't want a circular loop of this trigger so I don't want to do an actual Update to the table within this trigger (not sure if that's even possible). So is there a way I can do:

Create Trigger For Insert..blah..blah
--Assuming this is not a bulk insert, i.e. one-record-per-insert stmt
Select @foo = foo,@bar=bar,@pepe=pepe FROM OtherTable WHERE OtherTable_ID = Inserted.OTID;
--wondering if the below will 'slipstream' the fields instead of triggering this same trigger on the actual table
Update Inserted Set foo=@foo,bar=@bar,pepe=@pepe;

Thanks,
--Jim

 

[jrbarnett]

I would use an after insert trigger to populate the data in your new table. You can then use select statements to bring the data in from your other table joining to data in the inserted table into variables and populate data. Something like:

CREATE TRIGGER dbo.mytrigger AFTER INSERT ON dbo.mytable AS

  -- assumes only one row, f1 and f2 come from inserted table, foo and bar are from an external table
  declare @foo varchar (10)
  declare @bar varchar (10)

  select @foo = foo, @bar = bar
  from othertable JOIN inserted on othertable.id = inserted.OTID  

  insert into othertable (f1, f2, foo, bar)
  select inserted.f1, inserted.f2, @foo, @bar
  from inserted

If you need it to handle updates as well you can either create a separate update trigger doing something similar, or if running SQL 2008 or newer you can use a single trigger with the MERGE statement to handle the mix of update and insert operations.

John

 

[jsteph]

John,
Thanks, but what I'm looking to do is update the 'inserted' table, not othertable. So in your example I don't want to insert into 'othertable', but instead insert (but since this is an after-insert, I was thinking it would actually be an update) to the same table that the trigger is on.

I've heard of 'Instead Of' triggers, and I guess this is kind of like that.

In short, we have a compiled web app that inserts data into a table, we don't have the source code so can't alter the insert statement. We've added 3 fields to this table so we're trying to 'intercept' the insert statement and make it add the other 3 fields in one shot.

Is that possible?
Thanks,
--Jim