Delete Trigger - inserts a record 2

[mattpont]

Ok, I'm very new to triggers so please excuse my ignorance.

I want the following to happen:

- user deletes row from a database
- row gets inserted into a backup table

Is this possible with a Delete trigger?

Basically a client wants rows to be saved in another table when they delete (so they can possibly retrieve them). Be more careful with their deleting I say!

Thanks for any help.

 

[SQLSister]

Yes this is possible and in many cases a good idea. The key thing to remeber about a trigger is that inserts and updates and deletes can be in batches as well as one record at a time. It is therefore critical to write the trigger to process multiple rows of data not single rows of data.

You will find that there are two pseudotables available in triggers only called inserted (new values of the records affected) and deleted(original values of the records affected). In your case you will want to use the deleted table. Since all you want is a straight copy your task is relativiely simple (unless you have text, ntext fields (possibly image as well) becasue delted will not hold the values for those fields. All you really need as internal code is an insert statment using Select * from deleted instead of the values clause.

Look up the create trigger code and try to get this done and post your code if you have any problems getting it to work. DO not do this to production tables without testing on development first. Triggers are tricky to debug and you would not want to create a production problem.

"NOTHING is more important in a database than integrity." ESquared

 

[mattpont]

I couldn't get it to work with using "Select * From Deleted", but I managed to get a simple example working by using the code below:

CREATE TRIGGER dbo.userDelete ON tbl_test
FOR DELETE
AS

DECLARE @test_A AS varchar(50)
SET @test_A = (SELECT test_A FROM Deleted)
INSERT INTO dbo.tbl_test1 (test_A) VALUES (@test_A)

This works fine, but obviously may be a little long winded as I want to use this on a table with 20+ fields. Can someone point out a better way, or how I just use "SELECT *" please?

Thanks for your help.

 

[mattpont]

CREATE TRIGGER userDelete ON tbl_test
FOR DELETE
AS

INSERT INTO tbl_test1 (test_A) ((SELECT test_A FROM Deleted))


I managed it with this.

Thanks for your guidance SQLSister!

 

[SQLSister]

the structure is like so:

INSERT INTO yourtable (field1, field2, field3, deletedby, deletedate)
SELECT field1, field2, field3 , system_user, getdate()FROM Deleted

What you want is another table with the exact same structure as your existing table (not all in one file das you have it) because if you ever need to reinsert the records you want them in separate fields of the same datatype as your current table. Do not copy over the identity property if you use it. Also don't bother with timestamp fields (different from datetime, you will need those). Always specify the exact columns you want to insert to and the ones in the select statment.Select * is a bad habit and you should never ever use it. That way if someone re-orders the columns in one table or the other or adds a column to one table but not to the other, your trigger will not break. You might also consider adding columns for deleted date and the user who did the deleting (you'll see I put this inthe sample code. This are very handy to know if you need to track a delete that went very badly wrong.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

SQLSister,

Suppose you wanted to audit everything (Insert, update, and delete). Would you have separate audit tables for each action, or would you have a single audit table with an 'action' column to indicate insert, update or delete?

Ex:

Employee table with EmployeeId, Name, eyecolor, shoesize, etc...

Would you have 3 audit tables? Employee_Insert, Employee_Update, Employee_Delete

Or would you have just one audit table? Employee_History The Employee_History table (in this scenario) would probably have a TinyInt column to indicate the action.

Just curious.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

I can see arguments both ways. Our system here has only one and stores the indivdual old values and new values for everything. And it works well enough. But I could see that separate tables might improve performance. Would have to test to be sure.

At any rate I think that audit tables are an excellent idea (and they have saved me more than once (never forget to highlight the whole where clause when running a query) as well as saved our company lots of time recoving from mistakes. In our case audit tables are a client requirement and I'm pretty sure that they are a legal requirement for some businesses.

"NOTHING is more important in a database than integrity." ESquared