Single Trigger for both Insert and Update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Single Trigger for both Insert and Update

Author

Topic

raghulvarma
Starting Member

21 Posts

Posted - 2009-05-02 : 04:07:58

I have a parent table and a child table
when the records in the parent table is inserted then the records in the child table has to be inserted with the same records that has been inserted in the parent table, and in the same wise when the records in the parent table is updated then the records in the child table has to be updated with the same records as that of the parent.
so for that I have written a trigger,
but when the records is inserted or updated I could not see any change in the child table


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter TRIGGER [dbo].[Trg_Repair_Estimate_Insert_Update]
ON [dbo].[Repair_Estimate]
FOR INSERT,UPDATE 
AS
BEGIN
DECLARE @Trdsqncbin bigint,
@CntnrID bigint,
@cntnrno varchar(11),
@actvtytcd varchar(10),
@actvtynm varchar(50),
@actvtydt datetime,
@lctntcd varchar(20),
@mdfddt datetime,
@usrbin bigint
SET @Trdsqncbin = (SELECT mx_no from max_sno where tbl_nm='trade_history')
SET @CntnrID = (SELECT cntnr_bin from inserted)
SET @cntnrno = (SELECT cntnr_cno from inserted)
SET @actvtytcd = (SELECT rpr_estmt_bin from inserted)
SET @actvtynm = 'REPAIR ESTIMATE'
SET @actvtydt = (SELECT rpr_estmt_dt from inserted)
SET @lctntcd = null
SET @mdfddt = getdate()
SET @usrbin=null
if  exists (select cntnr_cno from inserted)
begin
UPDATE trade_history set actvty_dt=@actvtydt where trd_sqnc_bin=@Trdsqncbin and actvty_nm=@actvtynm
end
else
begin
INSERT into trade_history (trd_sqnc_bin,cntnr_bin,cntnr_no,actvty_tcd,actvty_nm,actvty_dt,lctn_tcd,mdfd_dt,usr_bin)values(@Trdsqncbin,@CntnrID,@cntnrno,@actvtytcd,@actvtynm,@actvtydt,@lctntcd,@mdfddt,@usrbin)
end
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-02 : 05:29:31

In case of INSERT or UPDATE the table inserted is always populated.
In case of UPDATE the table deleted is populated with old values and the table inserted has new/updated data.
So your IF has to check if exists deleted ...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-02 : 05:41:14

Also consider that your trigger has problems if there are more than one record is inserted or updated at a time.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources