• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

Can INSTEAD OF trigger see multi row IDENTITY column inserts

I am writing an auditing INSTEAD OF UPDATE trigger, and want to perform an INSERT and then cascade the changes to the PK to child tables based on the PK-FK

To cascade I need to find the IDENTITY values from the newly Inserted rows so the question is, can an INSTEAD OF trigger ever see the new IDENTITY values, or will I need to perform the INSERT and then implement an AFTER INSERT trigger to see the new Identity values (probably in the inserted table?)

I am unable to use @@IDENTITY as this is a muilti row trigger, and would prefer a set based solution than cursors.

Basially I am simulating the Cascade on Update rule of a foreign key, but with a soft delete and versioning involved.
0
rickchild
Asked:
rickchild
  • 3
  • 3
  • 2
  • +2
4 Solutions
 
RiteshShahCommented:
you can't directly use @@IDENTITY table, all you can use is INSERTED pseudo table.
0
 
chapmandewCommented:
I don't beleive you'll be able to use the INSERTED pseduo table here since it is an INSTEAD OF trigger and the action you perform doesn't actually get inserted.  You're better off using an AFTER INSERT trigger...then you can use it.
0
 
RiteshShahCommented:
yes chapman is absolutely write, I missed to write "use after insert" rather than instead of
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
momi_sabagCommented:
you can do this:
perform the insert statement and use the output clause in order to capture the identity values that were generated into a table variable
this will support multi rows and allow a set operation
0
 
rickchildAuthor Commented:
So if I were using an AFTER UPDATE trigger, would the inserted table be modified following an insert with the trigger, or will it always refer to the original inserted table?

For example in this case, both selects are the same, whereas I am looking so select the very last rows inserted, not the insert which kicked off the trigger.

BEGIN Trigger

select * from inserted
INSERT INTO TABLE VALUES
select * from inserted

END


Maybe I need two  AFTER triggers?  AFTER UPDATE followed by AFTER INSERT?
0
 
chapmandewCommented:
You'd use the same trigger, but they'd be in different scopes...and you'd have to have recursive triggers on ....

select * from inserted  --original inserted values
INSERT INTO TABLE VALUES  --will call the SaME trigger again, this time with NEW inserted values
select * from inserted  --once the execution of the 2nd iternation of the trigger completes, this will run again..

does that make sense?
0
 
Igor-KCommented:
In SQL 2005 and onwards you can use construction
insert into ....
ouput

I would recommend this way.
In SQL 2000 you would need to insert into your base table and then join to it to get identities.
If you insert only one row then @@identity, or scope_identity() should be sufficient (prefer second one).

But in any case you need first to insert into the base table.  Only insertion of the data will generate the identity.

create table #t(id int not null identity(1,1),m nvarchar(20))
 
go
declare @m table(id int not null,m nvarchar(20))
 
insert into #t(m)
output inserted.id,inserted.m
into @m
select 'a'
union all
select 'b'
 
select * from @m
go

Open in new window

0
 
momi_sabagCommented:
just use my suggestion and it will make your life simple
0
 
rickchildAuthor Commented:
Thanks guys, just having a go at the solutions now, but it looks like recursive after trigger, and also the suggestion from momi will work.
0
 
rickchildAuthor Commented:
Thanks guys, will up the points and split as a couple of good answers.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now