trigger for archiving (in sql 7)

[mgriffith]

i need to archive any changes made to one of my tables...i thought i might be able to do it with a trigger, but i'm beginning to think otherwise....here is the code for my trigger:

---------------------------------

CREATE TRIGGER tblRunCardProcessData_RevisionTrigger
ON dbo.tblRunCardProcessData
FOR UPDATE

AS

DECLARE @rcid int
DECLARE @wcpid int
DECLARE @wcpval varchar(50)
DECLARE @wcptol varchar(50)

BEGIN
SELECT
@rcid = RunCard_ID,
@wcpid = WCProcess_ID,
@wcpval = WCProcess_val,
@wcptol = WCProcess_tol
FROM
tblRunCardProcessData
WHERE
RunCardProcessData_ID = Inserted.RunCardProcessData_ID
END

BEGIN
INSERT INTO
tblRevisions

( RunCard_ID, WCProcess_ID, old_WCProcess_val, old_WCProcess_tol )

VALUES
( @rcid, @wcpid, @wcpval, @wcptol )
END

---------------------------------

this won't even compile...if i delete my where clause, it will compile, but i'm not sure if it is working, because i can't get a reference to the inserted row then. any ideas on what the syntax error could be there (it's sql 7)

another question, will this fire before the insert, or should i just forget about using a trigger and put the logic in my insert stored procedure?

thanks all.
mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[mgriffith]

nevermind...i got it...i'll post code in case anyone else is doing same thing


-----------------
CREATE TRIGGER tblRunCardProcessData_RevisionTrigger
ON dbo.tblRunCardProcessData
FOR UPDATE

AS

DECLARE @rcid int
DECLARE @wcpid int
DECLARE @wcpval varchar(50)
DECLARE @wcptol varchar(50)


BEGIN
SELECT
@rcid = RunCard_ID,
@wcpid = WCProcess_ID,
@wcpval = WCProcess_val,
@wcptol = WCProcess_tol
FROM
deleted
END

BEGIN
INSERT INTO
tblRevisions

( RunCard_ID, WCProcess_ID, old_WCProcess_val, old_WCProcess_tol )

VALUES
( @rcid, @wcpid, @wcpval, @wcptol )
END

------------------------------ mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[tlbroadbent]

Mike,

How about a simpler version? We tend to use too many procedural processes in SQL programming. There is no need for the variables and the two step process.

CREATE TRIGGER tblRunCardProcessData_RevisionTrigger
ON dbo.tblRunCardProcessData
FOR UPDATE
AS

BEGIN
INSERT INTO
tblRevisions
SELECT
RunCard_ID,
WCProcess_ID,
WCProcess_val,
WCProcess_tol
FROM
deleted
END Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[mgriffith]

thanks for the tip...that's actually what i tried originally, but for some reason i couldn't get it to work....might it have something to do with sql 7? i also haven't been able to do joins on updating (in other projects, which were solved using variables like above or cursors) mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[tlbroadbent]

The code I provided works in SQL 7 and SQL 2000. Updates with joins are certainly possible. Of course, there are limitations and the syntax must be correct. Most SQL problems can be solved without resorting to cursors. However, cursors and variables have their palce. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.