Strange problem with trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Strange problem with trigger

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2007-11-19 : 18:11:59

Hi there,
Just would like to get some feedback/ideas on why this would happen:

History,
We have moved our database server to a new hosting environment. The Server has been set up exactly the same as the previous environment. SQL 2000 sp4 on both. We restored the MASTER and all databases etc. All works well.

Problem,
For some databases, there is a trigger on a table called tbl_menu

CREATE TRIGGER tr_tbl_Menu_p_RegenerateMenus
ON tbl_Menu
FOR DELETE, INSERT, UPDATE
AS
BEGIN
DELETE FROM tbl_Menu_GenControl
END

This works fine on the old server...but fails on the new server. I resolved the issue by either;
a) adding a semicolon to the end of the DELETE FROM line
or
b) removing the BEGIN and END lines.

Really weird...its as though some special character exists after the DELETE FROM line and before the END.

I had to fix this on 50+ databases.

Any ideas why the need for a semicolon?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-19 : 22:57:55

Do they have same version?

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2007-11-19 : 23:42:10

the same sql version, same operating system.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources