April 20, 2009 at 4:28 pm
Have a very simple trigger that has worked flawlessly for years on 2000.
Moved the database to a 2005 server - now when the value of the field that is checked is changed to '01', not '02', the table won't update unless I disable the trigger. Which makes no sense - when cnahged to '01' the trigger should simply fall through. I am going to try adding a begin/end around the raiserror but I didn't think I should need to.
Can't find anything anywhere that explains this behavior. I know this doesn't get every record but the underlying code only updates a single record.
ALTER TRIGGER [SYS7334].[F98OWSEC_U] ON [SYS7334].[F98OWSEC]
FOR UPDATE
AS
DECLARE @SCUSER VARCHAR(10)
DECLARE @SCEUSER CHAR(2)
IF UPDATE(SCEUSER)
BEGIN
SELECT
@SCUSER = RTRIM(SCUSER),
@SCEUSER = SCEUSER
FROM inserted
IF @SCEUSER = '02'
RAISERROR(50007,10,1,@SCUSER)
END
Thanks!
Dave Schlieder
April 20, 2009 at 6:56 pm
As an FYI - I enclosed the raiserror with a begin...end block and my problem seems to have gone away - but I can't find any reference that this is needed or any type of a change in SQL 2005.
From MSDN:
"SQL Server 2005 Books Online (November 2008)
Using BEGIN...END
The BEGIN and END statements are used to group multiple Transact-SQL statements into a logical block. Use the BEGIN and END statements anywhere a control-of-flow statement must execute a block of two or more Transact-SQL statements.
For example, when an IF statement controls the execution of only one Transact-SQL statement, no BEGIN or END statement is needed:"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply