Creating Delete Trigger

Question

Post reply

Creating Delete Trigger

Tariq Changgez

Old Hand

Points: 399
More actions
September 4, 2004 at 12:22 am

#360857

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

eiddie dredd SSC Rookie Points: 43 More actions · Answer 1

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

Tariq Changgez Old Hand Points: 399 More actions · Answer 2

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

eiddie dredd SSC Rookie Points: 43 More actions · Answer 3

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

Tariq Changgez Old Hand Points: 399 More actions · Answer 4

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

Kelvin Lush SSCrazy Points: 2602 More actions · Answer 5

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!

Tariq Changgez Old Hand Points: 399 More actions · Answer 6

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 target table is wrong.

Kelvin Lush SSCrazy Points: 2602 More actions · Answer 7

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

Tariq Changgez Old Hand Points: 399 More actions · Answer 8

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