determine if any error occurs in trigger from out of trigger

  • 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

  • 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

  • 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.

    _____________
    Code for TallyGenerator

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply