Getting error while creating a trigger with INSTEAD OF UPDATE

[ramesh1210]

please help me,

I had created a table,

CREATE TABLE THETABLE
(
id_num int IDENTITY(1,1),
DT datetime,
NM varchar(30)
)


and then created a trigger on this table


CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.ID = inserted.ID
GO

But,I got the error while creating the trigger like this

Server: Msg 170, Level 15, State 1, Procedure myTRIGGER, Line 2
Line 2: Incorrect syntax near 'INSTEAD'.

please give me a solution

[azimmer]

please help me,

I had created a table,

CREATE TABLE THETABLE
(
id_num int IDENTITY(1,1),
DT datetime,
NM varchar(30)
)


and then created a trigger on this table


CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.ID = inserted.ID
GO

But,I got the error while creating the trigger like this

Server: Msg 170, Level 15, State 1, Procedure myTRIGGER, Line 2
Line 2: Incorrect syntax near 'INSTEAD'.

please give me a solution

in your table you use id_num, while in the trigger ID as column name use id_num in the trigger - it works fine (do not use keywords/reserved words as column names, it always leads to confusion)

[ramesh1210]

in your table you use id_num, while in the trigger ID as column name use id_num in the trigger - it works fine (do not use keywords/reserved words as column names, it always leads to confusion)

Thanks for your help,

after changing id to id_num, this is showing the same error.

i had updated the trigger like this:


CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.id_num = inserted.id_num
GO


But it is showiing same error,

Server: Msg 170, Level 15, State 1, Procedure myTRIGGER, Line 2
Line 2: Incorrect syntax near 'INSTEAD'.


Please note that it is showing the error in Line 2:INSTEAD

[azimmer]

Thanks for your help,

after changing id to id_num, this is showing the same error.

i had updated the trigger like this:


CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.id_num = inserted.id_num
GO


But it is showiing same error,

Server: Msg 170, Level 15, State 1, Procedure myTRIGGER, Line 2
Line 2: Incorrect syntax near 'INSTEAD'.


Please note that it is showing the error in Line 2:INSTEAD

I did note, in the previous version it did the same to me. I tried it, for me it shows no errors...

insert into thetable (DT,NM) values ('2007-01-01','A')
update thetable set NM='B' where NM='A'
select * from thetable
 

results:

id_num      DT                               NM                             
----------- -------------------------------------- ------
1           2007-08-17 10:07:36.110    B
 

So it does work. For me, at least.