Updates in Triggers
-
I'm having a problem with a trigger on a database table. Sometimes the trigger fires and other times it just fails. Could this be a know issue with SQL Server 2000 SP 3?
The trigger checks for inserts on the users table of our intranet database. After the insert the trigger uses an update statement to add additional information to the users table for the new user. Here the basic code:
CREATE TRIGGER Users_Insert
ON Users
FOR INSERT
AS
BEGIN
Declare
[heaps of variables]
[heaps of set statements]
-- Can't happen or trigger wouldn't have been fired
If @nRowcount = 0
Return
-- Multiple Inserts not allowed
If @nRowCount > 1
BEGIN -- Start Error Handling multiple inserts
RAISERROR('Only one row per update possible',16,10) WITH LOG
ROLLBACK
RETURN
END -- End Error Handling multiple inserts
SELECT @dName = dName,
@dPassword = dPassword,
@UserLocale = dUserLocale,
@UserId = dName
from inserted
SET @FULLNAME = [set fullname via other table]
SET @E_MAIL = [set e-mail via other table]
If @dPassword = '' or @dPassword is NULL
SET @dPassword = [set default password]
If @UserLocale = '' or @UserLocale is NULL
Begin
Select @UserLocale = Userlocale from xLanguages
inner join xUserAddon on LangId = Language1
where xUserAddOn.Unummer = @dname
If @UserLocale = '' or @UserLocale is NULL
Select @UserLocale = 'Deutsch'
End
BEGIN TRAN
Update Users Set
dUserAuthType = 'GLOBAL',
dFullName = @FULLNAME,
dEmail = @E_MAIL,
dPassword = @dPassword,
dUserLocale = @UserLocale
WHERE dName = @dName
If @@Error = 0
[some error handling to eventlog]
else
[some error handling]
ROLLBACK TRAN
[more code]
END
This is where the problems start because the return value of @@error always seems to be 0. Various authors point out that you should check @@error after each and every statement and react accordingly. I'd love to.
I've created an AddUser Sproc to debug the trigger with Visual Interdev, but when I do it that way, then the trigger always fires.
I've deleted my own entry various times and logged on to our Intranet so that the trigger fires and creates my entry in the users table, but it always suceeds. It's only when we start training people that the update statement in the trigger fails. A trace with the profiler didn't produce any hints where to look for problems, that's why I ended up here.
Looking forward to your hints and tips as always.
hot2use
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Some simple observatoions:
- It appears that you are setting the variable @nRowcount in the '[heaps of set statements]' if not, are you meaning @@ROWCOUNT?
- Since this is a trigger, you are already within a transaction. The BEGIN TRAN probably should not be used. Remember the rollback here will rollback everything in the transaction, not just the update.
--@@ERROR does work, but gets reset by every statement. If you need to use it after the first verification, save it to a local variable.
...but this didn't really help understand why the trigger sometimes fails. Sorry.
Guarddata-
-
Sorry. Snipped out too much code...
Way up at the beginning I assign Set @nRowCount = @@ROWCOUNT...
Yes, I know that if I rollback the transaction that everything will be rolled back. I tried using a SAVE TRAN TESTn and ROLLBACK TRAN TESTn for that reason, but then the trigger totally failed...
I only do @@error handling after each relevant DML statement.
Will have to look further...
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply