| Groups | Blog | Home |
sql server new users : Triggers in 2005, they changed it <GGGG>
Been doing triggers for years in sql 2000 now I find I got triggers that =
worked in a previous versions but I can no longer just pick up and = paste my code in sql 2005 triggers. One sample. I'm in mydb on myserver and have a table mytable with two fields = LastupdateOn and LastUpdateBy, I want to update these witha trigger. So = I right click on triggers in my database, select new trigger, now I get = this template that I fill out.=20 -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- Template generated from Template Explorer using: -- Create Trigger (New Menu).SQL -- -- Use the Specify Values for Template Parameters=20 -- command (Ctrl-Shift-M) to fill in the parameter=20 -- values below. -- -- See additional Create Trigger templates for more -- examples of different Trigger statements. -- -- This block of comments will not be included in -- the definition of the function. -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- Author: Robert Dufour -- Create date: Nov 17 2005 -- Description: Used to set the lastupdate fields -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CREATE TRIGGER trg_Mytable_ins_upd=20 ON Mytable AFTER INSERT,UPDATE AS=20 BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here Update Mytable Set LastUpdateBy =3D Suser_sname(), LastUpdateOn =3D GetDate() Where Exists (select * from inserted where inserted.Id=3D Mytable.Id) END GO This worked fine in 2000, in 2005 now I find I have to execute this = query to create the trigger and when I do I get following error message Msg 8197, Level 16, State 4, Procedure trg_Mytable_ins_upd , Line 6 Object 'Mytable' does not exist or is invalid for this operation. What gives here? Why does Mytable that I have already created and is = correct not get recognized. What do I need to do to create a trigger = now. Any help appreciated.
Works fine for me, although I would probably have written the trigger
like this myself: create trigger trg_Mytable_ins_upd on Mytable for insert, update as set nocount on; update mytable set LastUpdateBy = SYSTEM_USER, LastUpdateOn = CURRENT_TIMESTAMP from mytable t inner join inserted i on i.ID = t.ID; set nocount off; go It may be a dumb question, but are you sure the mytable table exists before you run the create trigger statement? Is mytable actually a table or is it a view? Can you post the schema for mytable? Are you in the correct DB at the time you try to execute the create trigger statement? -- *mike hodgson* blog: http://sqlnerd.blogspot.com [quoted text, click to view] Bob wrote:
> Been doing triggers for years in sql 2000 now I find I got triggers > that worked in a previous versions but I can no longer just pick up > and paste my code in sql 2005 triggers. One sample. > I'm in mydb on myserver and have a table mytable with two fields > LastupdateOn and LastUpdateBy, I want to update these witha trigger. > So I right click on triggers in my database, select new trigger, now I > get this template that I fill out. > > > -- ================================================ > > -- Template generated from Template Explorer using: > > -- Create Trigger (New Menu).SQL > > -- > > -- Use the Specify Values for Template Parameters > > -- command (Ctrl-Shift-M) to fill in the parameter > > -- values below. > > -- > > -- See additional Create Trigger templates for more > > -- examples of different Trigger statements. > > -- > > -- This block of comments will not be included in > > -- the definition of the function. > > -- ================================================ > > SET ANSI_NULLS ON > > GO > > SET QUOTED_IDENTIFIER ON > > GO > > -- ============================================= > > -- Author: Robert Dufour > > -- Create date: Nov 17 2005 > > -- Description: Used to set the lastupdate fields > > -- ============================================= > > CREATE TRIGGER trg_Mytable_ins_upd > > ON Mytable > > AFTER INSERT,UPDATE > > AS > > BEGIN > > -- SET NOCOUNT ON added to prevent extra result sets from > > -- interfering with SELECT statements. > > SET NOCOUNT ON; > > -- Insert statements for trigger here > > Update Mytable > > Set LastUpdateBy = Suser_sname(), > > LastUpdateOn = GetDate() > > Where Exists (select * from inserted where inserted.Id= Mytable.Id) > > END > > GO > > This worked fine in 2000, in 2005 now I find I have to execute this > query to create the trigger and when I do I get following error message > > Msg 8197, Level 16, State 4, Procedure trg_Mytable_ins_upd , Line 6 > > Object 'Mytable' does not exist or is invalid for this operation. > > > > > > What gives here? Why does Mytable that I have already created and is > correct not get recognized. What do I need to do to create a trigger > now. Any help appreciated. > > > > Bob > > > > > >
Hi Bob,
The syntax seems find. =20 Could you be in the wrong database? Please post he DDL and I can test = some more. kind regards Greg O ------- Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP AGS SQL 2005 Utilities, over 20+ functions http://www.ag-software.com/?tabid=3D38 [quoted text, click to view] "Bob" <bdufour@sgiims.com> wrote in message = Been doing triggers for years in sql 2000 now I find I got triggers =news:OCtEay86FHA.1020@TK2MSFTNGP15.phx.gbl... that worked in a previous versions but I can no longer just pick up and = paste my code in sql 2005 triggers. One sample. I'm in mydb on myserver and have a table mytable with two fields = LastupdateOn and LastUpdateBy, I want to update these witha trigger. So = I right click on triggers in my database, select new trigger, now I get = this template that I fill out.=20 -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- Template generated from Template Explorer using: -- Create Trigger (New Menu).SQL -- -- Use the Specify Values for Template Parameters=20 -- command (Ctrl-Shift-M) to fill in the parameter=20 -- values below. -- -- See additional Create Trigger templates for more -- examples of different Trigger statements. -- -- This block of comments will not be included in -- the definition of the function. -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- Author: Robert Dufour -- Create date: Nov 17 2005 -- Description: Used to set the lastupdate fields -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CREATE TRIGGER trg_Mytable_ins_upd=20 ON Mytable AFTER INSERT,UPDATE AS=20 BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here Update Mytable Set LastUpdateBy =3D Suser_sname(), LastUpdateOn =3D GetDate() Where Exists (select * from inserted where inserted.Id=3D Mytable.Id) END GO This worked fine in 2000, in 2005 now I find I have to execute this = query to create the trigger and when I do I get following error message Msg 8197, Level 16, State 4, Procedure trg_Mytable_ins_upd , Line 6 Object 'Mytable' does not exist or is invalid for this operation. What gives here? Why does Mytable that I have already created and is = correct not get recognized. What do I need to do to create a trigger = now. Any help appreciated.
Thanks to you both, I tried again and now it seems to work OK. NO =
change, weird. I must have done something wrong but still don't know = what. Oh well. Thanks=20 Bob [quoted text, click to view] "Bob" <bdufour@sgiims.com> wrote in message = Been doing triggers for years in sql 2000 now I find I got triggers =news:OCtEay86FHA.1020@TK2MSFTNGP15.phx.gbl... that worked in a previous versions but I can no longer just pick up and = paste my code in sql 2005 triggers. One sample. I'm in mydb on myserver and have a table mytable with two fields = LastupdateOn and LastUpdateBy, I want to update these witha trigger. So = I right click on triggers in my database, select new trigger, now I get = this template that I fill out.=20 -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- Template generated from Template Explorer using: -- Create Trigger (New Menu).SQL -- -- Use the Specify Values for Template Parameters=20 -- command (Ctrl-Shift-M) to fill in the parameter=20 -- values below. -- -- See additional Create Trigger templates for more -- examples of different Trigger statements. -- -- This block of comments will not be included in -- the definition of the function. -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- Author: Robert Dufour -- Create date: Nov 17 2005 -- Description: Used to set the lastupdate fields -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CREATE TRIGGER trg_Mytable_ins_upd=20 ON Mytable AFTER INSERT,UPDATE AS=20 BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here Update Mytable Set LastUpdateBy =3D Suser_sname(), LastUpdateOn =3D GetDate() Where Exists (select * from inserted where inserted.Id=3D Mytable.Id) END GO This worked fine in 2000, in 2005 now I find I have to execute this = query to create the trigger and when I do I get following error message Msg 8197, Level 16, State 4, Procedure trg_Mytable_ins_upd , Line 6 Object 'Mytable' does not exist or is invalid for this operation. What gives here? Why does Mytable that I have already created and is = correct not get recognized. What do I need to do to create a trigger = now. Any help appreciated.
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |