CODEWITHSUNDEEP
sql-server-2005triggers
Pavitar
Pavitar

Reputation: 4374

Select from another table within an Insert Trigger

I am maintaining an audit table, where in I have a parent table and it's child table.I want to insert the primary key of the parent audit table into it's child audit table.
Should I be declaring a "before insert" instead of a "for insert" trigger. Here's my code: 

CREATE trigger [trgAudtblChild] On [tblChild]
for Insert 
as
BEGIN

declare @serNo  bigint 
declare @expSerNo  int 
declare @postPercent  numeric  (12, 2) 
declare @prdSAPid  varchar  (50) 
declare @lastUpdatedBy  int 
declare @lastUpdatedOn  smalldatetime 

SELECT 
 --@serno = serno,
 @expSerNo = expSerNo ,
 @postPercent = postPercent ,
 @prdSAPid = prdSAPid ,
 @lastUpdatedBy = lastUpdatedBy ,
 @lastUpdatedOn = lastUpdatedOn
FROM INSERTED

select @serno = max(at_serno) from AT_tblParent

insert into AT_tblChild(serNo, expSerNo, postPercent
                       , prdSAPid, lastUpdatedBy, lastUpdatedOn
                       , change_column_index) values(
                                  @serNo, @expSerNo, @postPercent
                          , @prdSAPid, @lastUpdatedBy, @lastUpdatedOn
                          , 'INSERTED')

End
Return

The above code, does not work and puts the table into transaction.

Upvotes: 0

Views: 719

Answers (1)

Nilish
Nilish

Reputation: 1076

Before Trigger - When you want to Intercept the data before it actually gets Inserted in Table.

For Trigger - Your record is Inserted but can still modify it.

The only difference is that about record is actually Inserted or not.

Back to the original Query

In you above mentioned situation, you should not use Before Trigger. Consider a case, when your Parent Table record Insertion in under some Transaction and same Transaction gets Rollbacked. In that case, It will crash for the Foreign key constraint. Because you will try to Reference a Foreign key Record of Parent Table into Child Table during Insertion which does not exist in Parent Table.

Upvotes: 1

Related Questions