Help with triggers . - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Help with triggers .

Author

Topic

admin001
Posting Yak Master

166 Posts

Posted - 2003-04-09 : 02:14:11

Hello Friends ,

Would like to have help on the following :

I have database with two tables t1 and t2 in it . I want to create a trigger such that when ever there is an update on a specific record in t1 it should create or write a new entry in t2 . So if there is another update on the same record in t1 , it will write again a new entry in t2 . So total entries in t2 will be two for the same record in t1 .
Is this possible and how could we possibly do it . I am stuck badly with the dev. team . The server is SQL 2000 with SP2 .

Would be of great help to me .

Thank you very much
Admin001 .

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-09 : 02:27:19

You should really read Books On Line (BOL) and you would have discovered the wonders of the INSERTED and DELETED tables
that only exist within a trigger. Beware column types!


CREATE TRIGGER trUpdateT1
ON T1
FOR UPDATE
AS
BEGIN

INSERT T2 (Column1, Column2)
SELECT Column1, Column2 from INSERTED 

END

DavidM

"SQL-3 is an abomination.."

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 09:10:29

Are you trying to log update/delete history? This is a common thing for most transactional dbs. I don't know if there are any articles on this site, but it would be worth a check. Here's a sample of what we use. I would suggest making a mirror copy of the table to track the changes. Just add Historical datetime columns to document when things got moved. The existance check is to determine the action (only because I combined update and delete in the same trigger...could've had 2 triggers):

Hope this helps.

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources