I have a problem that I can't quite figure out, but seems pretty simple to me.
Basically, our company has a large database of clients. We would like to divide them into three categories: new, active, and former. Right now we have a GIANT table with ~55,000 records that include all of our clients.
I would like to be able to create a trigger that basically says "On update of this column, move this client from one table to another". I have created six stored procedures: three of which insert all client records into a particular table and three of which delete a client from a table.
I have written a trigger:
CREATE TRIGGER masterSQL.ACTIVE_TO_FORMER ON USRCSQL
AFTER UPDATE
AS
IF UPDATE (XKILL)
BEGIN
IF (old.XKILL IS NULL) AND (new.XILL IS NOT NULL)
BEGIN
EXEC INSERTFORMERCLIENT @CASENUM = old.CASENUM
EXEC DELETEFROMACTIVE @CASENUM = old.CASENUM
END
END
GO
Which I would like to move an "active" client to the "former" table by calling the two stored procedures (assume they work). I don't think the "new" and "old" keywords are correct.
In pseudo-code this would be:
IF (the old value of column xkill is null) and (the new value of column xkill is not null) then
insert this record into the former database
delete it from the active database
Does anybody have any ideas? How exactly do you reference "old" and "new" values in MSSQL 2000? What am I doing wrong here?
Thanks a ton!
Basically, our company has a large database of clients. We would like to divide them into three categories: new, active, and former. Right now we have a GIANT table with ~55,000 records that include all of our clients.
I would like to be able to create a trigger that basically says "On update of this column, move this client from one table to another". I have created six stored procedures: three of which insert all client records into a particular table and three of which delete a client from a table.
I have written a trigger:
CREATE TRIGGER masterSQL.ACTIVE_TO_FORMER ON USRCSQL
AFTER UPDATE
AS
IF UPDATE (XKILL)
BEGIN
IF (old.XKILL IS NULL) AND (new.XILL IS NOT NULL)
BEGIN
EXEC INSERTFORMERCLIENT @CASENUM = old.CASENUM
EXEC DELETEFROMACTIVE @CASENUM = old.CASENUM
END
END
GO
Which I would like to move an "active" client to the "former" table by calling the two stored procedures (assume they work). I don't think the "new" and "old" keywords are correct.
In pseudo-code this would be:
IF (the old value of column xkill is null) and (the new value of column xkill is not null) then
insert this record into the former database
delete it from the active database
Does anybody have any ideas? How exactly do you reference "old" and "new" values in MSSQL 2000? What am I doing wrong here?
Thanks a ton!