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 it

Thats 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´catchable due to the severity.

I suggest taking a look in Erland´s Error handling script
http://www.sommarskog.se/error-handling-I.html#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 ?

Show quoteHide quote

Error handling in sql server 2000 within triggers is tricky business,
have a look at Erland's article.

http://www.sommarskog.se/error-handling-I.html#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
Show quoteHide quote

Show quote Hide quote

Exactly. And I'm not referring to the INSERTED virtual table. What am I
missing?


ML

---
http://milambda.blogspot.com/

also from OP

he's using the insert trigger to insert into another table, where he
get's the variable from I don't know

Perhaps Vikram can clarify this if he ever comes back. :)


ML

---
http://milambda.blogspot.com/

Extracting from linked tables into pivot
What's a good SQL server naming convention
Optimizing an IN clause
SQL 2k5 vs 2k - different results on same execution? ORDER BY prob
What is this called, and how to do it?