Trigger question
-
Good morning,
Being a complete newbie to SQL, I would like your advise on how to write a simple trigger to make sure, that after inserting a row in a table, certain fields of the row inserted would be changed to upper case. Thanks very much in advance.
-
create trigger ti_table1 on table1 for insert as
begin
declare
@maxcard int,
@numrows int,
@numnull int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
update table1
set col1 = upper(i.col1)
from inserted i, table1 a
where i.key_col = a.key_col
return
/* Errors handling */
error:
raiserror @errno @errmsg
rollback transaction
end
-
works great - thank you!
-
What's with all the declared but unused variables?
You've got an error handling block, but no error checking anywhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
i left these unused elements out - the main benefit of the code was the part
from inserted i, table1 a where i.keyid = a.keyid
that was the eye-opener
-
the 'unused element' is my standard template for creating a Trigger
-
Here is a simplier solution:
create trigger ti_table1 on table1 for insert
as
set nocount on
set xact_abort on
-- if an insert was performed but no rows where inserted, then exit.
IF @@rowcount = 0 RETURN
update table1
set col1 = upper(table1.col1)
from inserted
where table1.key_col = inserted.key_col
and inserted.col1 != upper(inserted.col1)
go
SQL = Scarcely Qualifies as a Language
-
KH - Have you ever tested your trigger error handling code?
My first experience with writing triggers was in SQL 6.5 or 7.0, and I used code like that (inhierited from a previous "expert") as a template. I went nuts trying to figure out why the error handling never worked in my triggers before I realized it never worked in the "expert's" triggers either. A trigger is considered part of the statement that caused the action, and a command in a trigger that causes an error will cause the entire statement, trigger and all, to be aborted. There is no point to checking @@ERROR in a trigger because if there is an error the trigger is aborted before the error-handling code can be executed.
At least that's how it was in earlier versions. If SQL 2000 works differently I would love to hear about it.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply