handle errors in trigger

How to handle error in trigger.
i m inserting record in other table in after insert trigger. but if insert
statement in trigger has error, procedure quits with error and i cannot use
@.@.error to check itThats an artifical error, but that the way you could handle it:
Create Table SomeTable
(
SomeColumn INT
)
Go
ALTER TRIGGER SomeTrigger
on SomeTable
FOR INSERT
AS
BEGIN
RAISERROR('SomeError',16,1)
PRINT @.@.ERROR
IF @.@.ERROR <> 0
PRINT 'Seems that an error occured.'
END
INSERT INTO SomeTable VALUES (1)
DELETE FROM SomeTable
SELECT * FROM SomeTable
Keep in mind that some error aren=B4catchable due to the severity.
I suggest taking a look in Erland=B4s Error handling script
http://www.sommarskog.se/error-hand...triggercontext.
HTH, jens Suessmeyer.|||I don't know exactly what you're doing but there are a couple of things
you may want to check before you do an insert.
If you're using variables in your values list check them before you
insert if they aren't null in case your table has colums with not null
defined.
But in general do have a look at Erland's article.|||Ya variable is null and table does not allow null, but error should be
trapable isnt it ?
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137149721.665336.187300@.g44g2000cwa.googlegroups.com...
> I don't know exactly what you're doing but there are a couple of things
> you may want to check before you do an insert.
> If you're using variables in your values list check them before you
> insert if they aren't null in case your table has colums with not null
> defined.
> But in general do have a look at Erland's article.
>|||Error handling in sql server 2000 within triggers is tricky business,
have a look at Erland's article.
http://www.sommarskog.se/error-hand...triggercontext.
But since you know that the variable is null then something like this
may avoid the error:
DECLARE @.var int
SET @.var = ISNULL(@.var, 1) /* the variable will be assigned the value
of 1 if it is null
or
IF(@.var) IS NULL
BEGIN
-- write all variables to a log table
INSERT INTO ...
RETURN
END|||The AFTER trigger fires *after* the insert - hence the name. In case of a
constraint violation (such as the nullability constraint) the the AFTER
trigger will not fire at all, since the insert is aborted.
Consider using an INSTEAD OF trigger or better yet - set values properly
before inserting them or use a deafult.
ML
http://milambda.blogspot.com/|||who says the variable is coming from inserted?|||I was guessing based on OP's narrative.
ML
http://milambda.blogspot.com/|||from OP
> but if insert statement in trigger|||> but if insert statement in trigger