Converting SQL 2000 Triggers to SQL 2005

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

Transact-SQL (2000)

Converting SQL 2000 Triggers to SQL 2005

Author

Topic

andretix
Starting Member

2 Posts

Posted - 2006-12-27 : 19:20:34

Hello,

I am trying to copy over a trigger that is in my SQL2000 instance over to a new machine running SQL-2005. I scripted the trigger using Query Analyser and then created a new trigger in SQL-2005.

I did not change any of the Syntax. Its a very simple trigger.

The trigger works fine in SQL-2000 but does not work in SQL-2005.

What has changed.

I know about the DDL vs DML triggers where DDL triggers are used to access server wide items such as Create table and Drop Table. I am not doing any create table functions or anything of the sort in my trigger.

I get the following error when ever I try to update a record in the table.

"The transaction ended in the trigger, the batch has been aborted"

Can anyone tell me what im doing wrong?

Here is the trigger:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
/*
TRIGGER CHECKS TO SEE IF USER HAS CHANGED THE APNO. IF YES, TRANSACTION IS ROLLED BACK AND ERROR MESSAGE IS DISPLAYED.
*/

CREATE TRIGGER [imsv7].[CHECK_APNO_CHANGE]
ON [imsv7].[APBLDG]

AFTER UPDATE
AS

DECLARE @ORIG_APNO VARCHAR(9)
DECLARE @NEW_APNO VARCHAR(9)
SET @ORIG_APNO = (SELECT APNO FROM DELETED)
SET @NEW_APNO = (SELECT APNO FROM INSERTED)

IF @NEW_APNO <> @ORIG_APNO
BEGIN
ROLLBACK TRANSACTION
RAISERROR (' You are not allowed to change the A/P #', 16, 1)
END
ELSE
BEGIN
COMMIT TRANSACTION
END

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-27 : 19:50:24

In 2005 you now get that error when you have a rollback in a trigger. It doesn't change the functionality in any way, you are just gauranteed to know about it in the code that performs the modification, whereas before if you didn't raise an error, you didn't get one. It's up to you if you want to still raise your own error as well.

I'd suggest you write the trigger this way (on either version). Use the UPDATE() function rather than your own code to check to see if the column was updated, and don't commit inside the transaction, the commit is going to happen any way and you should rather let the code running the modification decide whether or not to commit.

CREATE TRIGGER [imsv7].[CHECK_APNO_CHANGE]
ON [imsv7].[APBLDG]

AFTER UPDATE
AS

IF UPDATE(APNO)
BEGIN
ROLLBACK TRANSACTION
RAISERROR (' You are not allowed to change the A/P #', 16, 1)
END

Kristen
Test

22859 Posts

Posted - 2006-12-28 : 07:30:56

Note that you should write your triggers to handle more than one row - so the code that snSQL has offered is more "robust" in this regard than your:

DECLARE @ORIG_APNO VARCHAR(9)
DECLARE @NEW_APNO VARCHAR(9)
SET @ORIG_APNO = (SELECT APNO FROM DELETED)
SET @NEW_APNO = (SELECT APNO FROM INSERTED)

Kristen

andretix
Starting Member

2 Posts

Posted - 2006-12-28 : 09:36:30

I didnt know about the update() function.

Ill try this and see whats happens. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources