Creating Delete Trigger
-
I want a Delete Trigger that copies the deleted records into an archival table "TS_Deleted". Plus I need two more fields "DeletedDate" and DeletedBy" who's values I set by using GETDATE() and SUSER_SNAME.
My problem is that SQL server does not allow INSERT INTO statement where fields in both tables or not identical, whereas in my case there are two additional fields in the TS_Deleted table.
I don't want to create two additional required fields in the original table as it will increase the size of the table unnecessirily.
-
SIMPLE Trigger to DELETE and add the deleted to the different tables two or more
Create trigger trgDeltblA
On tblA
For Delete
As
Declare @UserName Varchar(500), @Title Varchar(50),
@ID int
Select @UserName =User,@ID = BiodataID From Deleted
Begin
insert into TS_Deleted (a,b,deleteddate,deletedby) values (@Title,@ID, getdate() , @UserName)
Update tableC set a = @User , b = @Title where a = 'myname' and title = 'your documnet' and id = 0983712
End
hope this helps
Kindest Regards,eiddie dredd
No Pain No Gain
-
I tried your code as shown below but now I am getting an error message when I delete a record in the original table:
"Insert Error: Column name or number of supplied values does not match table definition".
Please note that original table "TS" contains twently fields and target table named TS_Deleted contains only three fields: RecordNo, DeletedDate and DeletedBy. Only first field needs value from the original table while other two will be set through variables. Here is the code that I am using:
CREATE TRIGGER trTS_Deleted
ON TS
FOR DELETE
AS
Declare @RecordNo Int, @UserName Varchar (25)
Select @UserName =User, @RecordNo = RecNo From Deleted
Begin
insert into TS_Deleted (RecordNo,DeletedDate,DeletedBy) values (@RecordNo, getdate() , @UserName)
End
-
Sorry.... your insert is not enough values
the getdate do not return any value ...are you using 6.5? the solution applied to SQL7 or 2000
try add @ed datetime
Declare @RecordNo Int, @UserName Varchar (25),@ed datetime
Select @ed = getdate()
Select @UserName =User, @RecordNo = RecNo From Deleted
insert into TS_Deleted (RecordNo,DeletedDate,DeletedBy) values (@RecordNo, @ed , @UserName)
Kindest Regards,eiddie dredd
No Pain No Gain
-
Thanks for yoru help. I am using SQL ver. 2000.
In fact the first code was also OK. By mistake another Trigger was also active which was generating the error message. This time I made sure that all triggers on this table are deleted and then run the code. It worked fine this time.
God bless you.
Tariq
-
Other replies seem over complicated.
How about:
INSERT INTO TS_Deleted
SELECT *, GetDate(), USER
FROM deleted
Change the * to the actual columns you require and this will work where multiple rows are deleted whereas previous answers won't!
-
Hello Kelvin,
INSERT INTO TS_Deleted
SELECT * ...
though is very simple but it forces me to have all columns in the target table that exist in the original table. In my case the original table contains 20 columns while I am interested in only one of those 20 columns which is a unique ID key and is enough for me to find out which record it was.
Please correct me if my perception about existence of all columns in tar
get table is wrong. -
Your perception is indeed wrong.
As I said, change the * to the columns you require.
E.g.
INSERT INTO TS_Deleted
SELECT RecordNo, GetDate(), USER
FROM deleted
Your query seems to have changed since the original posting. You started off by implying you needed all columns plus two additional ones. In a later posting you say your TS_Deleted contains only three columns. Take your pick of my suggested solutions and adjust as necessary.
I still maintain that, of the other posted solutions, this is the only one that will work with multiple deletions - E.g. DELETE FROM TS WHERE RecordNo > 5
-
I agree that my perception was wrong. It worked the way you suggested.
I thank you as well as other members of the forum who helped me to
achieve the solution. God bless you all.Tariq
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply