Nested Triggers

Question

Nested Triggers

paul murfin

Grasshopper

Points: 19
More actions
August 14, 2006 at 4:24 am

#172069

Hi
When I insert a record into table A I want to call a trigger to insert a record into table B. Then on the insert into table B I want to call a trigger to insert a record into table C.
The primary keys on tables B and C are auto-increment no.s. When I am inserting the last record into table C I need to access data from both the record inserted into table A and table B.
I know in each trigger I can use the Inserted virtual table but in the second trigger how do I access data from the record inserted into table A ... e.g. the Insert virtual table from the previous nested trigger.
Hope I have explained that properly!!!!!
Thanks

Paul

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Paul,

There is an inherent problem in what you want to achieve. By putting a trigger on table B you are saying you want to handle inserts on table B in isolation from inserts on table A. What were to happen if someone inserted data into table B directly. How would the trigger have access to the inserted table that occurred on table A if nothing was inserted into table A?

It seems that what you might need is to insert a record in table B and C when you insert into table A.

In other words, have one trigger (on table A) and within that trigger insert into table B and immediately after that, insert into table C. That way you have access to the records from table A and B that you need to insert into table C.

Hope that helps,

Karl
source control for SQL Server

paul murfin Grasshopper Points: 19 More actions · Answer 2

Hi Karl

Yes that is what i was sort of planning on doing i'm just not sure how i get at the auto-increment key from table B - so that i can use it is table C ... i was sort of thinking something like (see below) ... will this work ?

Thanks

Paul

CREATE TRIGGER tr_INSERT_dog

ON Dog

FOR INSERT

AS

SET NOCOUNT ON

declare @ClientNo

declare @DogName

declare @GroupNo

SELECT @DogNo = (SELECT DogNo from Inserted)

SELECT @ClientNo = (SELECT ClientNo FROM Inserted)

SELECT @DogName = (SELECT DogName FROM Inserted)

**** table B

INSERT DogGroup VALUES (@DogName,@ClientNo)

**** table C note ... GroupNo is the auto increment key on table B - must be a better way of getting the just inserted key (GroupNo) from table B???????????????? than this select??????

SELECT @GroupNo = (SELECT GroupNo

FROM DogGroup

WHERE GroupName = @DogName

AND ClientNo = @ClientNo)

INSERT DogGroupMember VALUES (@GroupNo,@DogNo)

SQLZ SSChampion Points: 12872 More actions · Answer 3

Ah ok, I see.

To grab the last identity value that was generated you should use the @@identity or scope_identity() functions.

Generally I would recommend using scope_identity() as this is restricted to the session and scope.

So, immediately after doing the insert on DogGroup, do the following.

declare

@my_new_id int

select

@my_new_id = scope_identity() --returns the last identity inserted in any table by the current session. And is restricted to the scope of this trigger.

As in my previous post I would recommend you stick to using the one trigger on table A otherwise things could get messy with scopes and the fact that someone could directly insert into table B, which could ruin things.

Karl
source control for SQL Server

paul murfin Grasshopper Points: 19 More actions · Answer 4

Karl

That is PERFECT ... exactly what I needed just did not know how to access it ... I'm back writing a system for my girldfriend's dog boarding company after many years away from programming ! Things look a bit different to the way they were 10 years ago!!!!

Thanks very much - really appreciated!!!!

Paul