469,950 Members | 1,381 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Getting error while creating a trigger with INSTEAD OF UPDATE

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
Aug 16 '07 #1
3 2252
azimmer
200 Expert 100+
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)
Aug 16 '07 #2
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
Aug 17 '07 #3
azimmer
200 Expert 100+
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...

Expand|Select|Wrap|Line Numbers
  1. insert into thetable (DT,NM) values ('2007-01-01','A')
  2. update thetable set NM='B' where NM='A'
  3. select * from thetable
  4.  
results:
Expand|Select|Wrap|Line Numbers
  1. id_num      DT                               NM                             
  2. ----------- -------------------------------------- ------
  3. 1           2007-08-17 10:07:36.110    B
  4.  
So it does work. For me, at least.
Aug 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by M | last post: by
3 posts views Thread by takilroy | last post: by
12 posts views Thread by Bob Stearns | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.