Trigger - Incorrent Syntax

Question

Trigger - Incorrent Syntax

Minnu

SSCertifiable

Points: 6451
More actions
November 24, 2014 at 5:45 am

#295371

Hi Team,
Am using below trigger syntax, its showing error 'Incorrect syntax near Delete'
CREATE TRIGGER [Test_TRG] ON CUSTOMERS
FOR UPDATE, DELETE
AS IF UPDATE(col1) OR DELETE(col2)
BEGIN
INSERT INTO users values ('0789','Sheker','WT_WRO')
END
Please help me

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no such function as DELETE()

The UPDATE() function returns true if the column was included in the update. Since DELETE affects the entire row, there would be no point in having a function that checks whether the column was part of the delete. It would always be true.

What exactly is this trigger supposed to do? Insert exactly the same value into a table every time any row is updated or deleted?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Thank you for your info..

i want to trigger in two scenaios,

1) after update - insert statment

2) and before delete - insert statement.

please suggest in above scenaio

Minnu SSCertifiable Points: 6451 More actions · Answer 3

What exactly is this trigger supposed to do? Insert exactly the same value into a table every time any row is updated or deleted?

Yes,

Insert exactly the same value into a table every time any row is updated or deleted.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Odd requirement, but whatever...

Then this will do that

CREATE TRIGGER [Test_TRG] ON CUSTOMERS

FOR UPDATE, DELETE

AS

INSERT INTO users values ('0789','Sheker','WT_WRO')

Any time any row on the customers table is updated or deleted, the values 0789, 'Sheker','WT_WRO' will be inserted as a new row in the Users table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass