SQLserver 70 Deletes

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

I am interested if you know of a way to copy rows from table1 to table2 when the row is deleted from table 1. Table 2 being a copy of table1.

I am trying to keep the data from being lost and the application does not allow for this nor are they entertaining the idea of doing this.

I was thinking of removing the permissions for the users to delete from this table but the program blows out when it cant delete the row.

Any suggestions?

-- Anonymous, February 27, 2002

Answers

Paul,

Consider a delete trigger on table 1 that copies the row into table 2.

Hope this helps,

Eric

-- Anonymous, February 27, 2002


Paul,

To elaborate, you will need to use the table called "deleted" that is created for you by the delete trigger. The "CREATE TRIGGER" article in SQL Server Books Online explains it thusly: deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use: SELECT * FROM deleted

Hope this helps,

Eric

-- Anonymous, March 05, 2002


Moderation questions? read the FAQ