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
Microsoft SQL Server

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
MNelson831

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
tammieR

ASKER
It is actually datatype uniqueidentifier, can I still use @@identity
MNelson831

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')
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question