Link to home
Start Free TrialLog in
Avatar of tammieR
tammieR

asked on

Update Trigger inserts data into 2 tables with 2nd insert using data from 2 tables

Here is what I have.  I have 3 Tables. When a certain field in Table A gets updated to Yes I have an udpdate trigger that has an insert statement that puts the updated row (row that a field got changed to yes) into Table B. Now I need to insert some of the data from the updated row in Table A and I need the UniqueID of the newly inserted Row in Table B and insert the data from Table A and Table B into Table C.

Here is what I have so far and it work good. It inserts data from the row that was update in Table A into Table B:

CREATE TRIGGER ToInsertRow ON TableA
AFTER UPDATE
AS
IF UPDATE(YesNoColumn)
BEGIN
INSERT INTO  TableB(
      Column1,
      Column2)
SELECT inserted.Column1, inserted.Column4
FROM inserted
WHERE inserted.YesNoColumn = 'Yes'
END

Thanks in Advance
Avatar of MNelson831
MNelson831
Flag of United States of America image

Alter TRIGGER ToInsertRow ON TableA
AFTER UPDATE
AS

Declare @TableBid as integer -- or other relevant datatype if not using int for id

IF UPDATE(YesNoColumn)
BEGIN
INSERT INTO  TableB(
     Column1,
     Column2)
SELECT inserted.Column1, inserted.Column4
FROM inserted
WHERE inserted.YesNoColumn = 'Yes'

Select @TableBid = @@Identity

Insert into TableC
Select fields from inserted inner join TableB on KeyField = KeyField where TableBid = @TableBid and other stuff





END
Avatar of tammieR
tammieR

ASKER

It is actually datatype uniqueidentifier, can I still use @@identity
Nope.

IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


SELECT IDENT_CURRENT('TableB')
Avatar of tammieR

ASKER

Below is what I have now for the second insert, but I am not sure how to use or where to place the
SELECT IDENT_CURRENT('TableB') Statement

INSERT INTO TableC(      
      Column1, Column2, Column3)

SELECT inserted.Column2, inserted.Column5
FROM inserted
INNER JOIN TableB on KeyField = KeyField where TableBid = @TableBid and other stuff

Do I still have to delare a variable and use this line?
Select @TableBid =  IDENT_CURRENT('TableB')

Column1 and Column2 are from the from the updated row in TableA
Column3 needs to be the UniqueIdentity value from the row inserted into TableB

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial