SQL Server 2000 Trigger

[fatmosh]

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!

 

[SQLSister]

You reference the old and new values by using the psuedotables called inserted and deleted. Deleted and inserted psuedotables are only available in triggers. They work just like regular tables in T-SQL code.

Example SQL statment:

Insert into table2 (Col1, Col2, Col3)
Select col1, col2, getdate() from deleted

This would insert the old information into a another table like an audit table if a record is changed or deleted.

Hope that helps.

Questions about posting. See faq183-874

 

[fatmosh]

Thanks for the quick reply, but unfortunately it is still not working. I changed the code to:

CREATE TRIGGER masterSQL.ACTIVE_TO_FORMER ON USRCSQL
AFTER UPDATE
AS
IF UPDATE (XKILL)
BEGIN
select * from deleted as d
select xkill, casenum from inserted as i
IF (d.XKILL IS NULL) AND (i.XKILL IS NOT NULL)
BEGIN
EXEC INSERTFORMERCLIENT @CASENUM = d.CASENUM
EXEC DELETEFROMACTIVE @CASENUM = d.CASENUM
END
END

GO

...but now it says "Line 10: Incorrect syntax near '.'.", which is where the d.CASENUM is. Why is it not recognizing d as an alias? If I take out both d.CASENUMs it says "Server: Msg 107, Level 16, State 2, Line 8
The column prefix 'd' does not match with a table name or alias name used in the query."

Any ideas?

Thanks in advance