determine if any error occurs in trigger from out of trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » determine if any error occurs in trigger from...

determine if any error occurs in trigger from out of trigger

Rate Topic

Display Mode

Topic Options

Author

Message

samiee.gd7

Posted Monday, October 08, 2012 3:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 12:08 AM
Points: 2, Visits: 13

i have an Insert command on Table in which fires a trigger. i wanna know if any error occurs in the trigger

the trigger itself contains an Insert statement which may cause "Violation of PRIMARY KEY constraint"

how can i be notified if "Violation of PRIMARY KEY constraint" error has occur when inserting into Table

Post #1369691

GSquared

Posted Monday, October 08, 2012 6:58 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

You'll get an error message from the SQL Server engine if that happens.

If you want to do more with it, check out @@Error, and Bing/Google "sql 2000 error-handling" for numerous articles on the subject. You'll find something that aligns with your particular needs.

I'm assuming you're using SQL 2000, because of the forum you posted in. If it's a later version, you can also look at the T-SQL implementation of "Try/Catch". Data here: http://msdn.microsoft.com/en-us/library/ms175976.aspx But that doesn't work in SQL 2000, only in 2005 and later.

If you want more specific help, post the script of the trigger, the structure of the two tables, and a description of what you want to do when such an error comes up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1369788

Sergiy

Posted Tuesday, October 09, 2012 6:46 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:01 PM
Points: 4,540, Visits: 8,184

In a trigger it's pretty easy to check if the data could cause PK violation prior to running INSERT.
And divert execution if that's the case.
Or exclude the data which may cause PK vioaltion.

Way easier than trying to catch errors.

Trouble is - in case of such an error the whole transaction will be rolled back, together with any action you'll try to perform in the trigger.

Post #1370645

« Prev Topic | Next Topic »

Permissions