CODEWITHSUNDEEP
sql-servertriggers
GettingStrongerEveryDay
GettingStrongerEveryDay

Reputation: 31

T-SQL Conceptual trigger issue

I am placing a trigger on a table in a vendor's software (on our machine of course) that will create a record of checks written in a specific format for our bank (because of the type of business we are in, we need up to the minute reporting on checks written for fraud management). I've already written a robust application which does the job, however we are very apprehensive about any impact to the main software. For example, if anything goes wrong in the trigger, the whole application insert process (with multiple files) goes south, and we cannot afford to mess up production with a side application.

So we're going to write this check tracking record out with minimal data and handle it in a way removed from the application.

The question: is there a way to insure that even if our basically blank record cannot write that the INSERT in our vendor's software still goes on no matter what?

Upvotes: 1

Views: 104

Answers (3)

M.Ali
M.Ali

Reputation: 69524

You need to create an Instead of Insert Trigger , which validates any data before it gets inserted. Do checks for data validation and once satisfied do the insert than, otherwise if it fails the validation checks just send an user friendly error message back, telling end users how to fix the problem and attempt again.

Afterall you dont want your customers to just walk away just because your trigger doesnt like it. 

CREATE TRIGGER tr_Trigger_Name
ON Table_Name
INSTEAD OF INSERT
AS
BEGIN
   BEGIN TRY 

            /*DO your data validation checks here*/

              IF (/*check No 1*/)
               BEGIN
                 RAISERROR('Send this message to user to guide what to do', 16, 1)
               END

              IF (/*check No 2*/)
               BEGIN
                 RAISERROR('Send this message to user to guide what to do', 16, 1)
               END

   /*Once Validation is done and you are happy to continue with the transaction*/

   BEGIN TRANSACTION
     INSERT /* Insert data what ever you want*/

   COMMIT TRANSACTION
   END TRY


   BEGIN CATCH
     /*If something has gone wrong During Validation of Data Insert*/

    IF @@TRANCOUNT > 0 
      ROLLBACK TRANSACTION

        /*DO other stuff here in case of an error or validation faliure */


   END CATCH

END

Upvotes: 0

DougM
DougM

Reputation: 2888

Yes -- don't use a trigger.

Instead of, say:

CREATE TRIGGER CheckSpy ON checks 
FOR AFTER INSERT AS 
INSERT INTO dbo.Spy(CheckID) 
SELECT CheckID
FROM INSERTED 
WHERE type="Fraud"

You can just write a stored procedure that pulls the inserted records AFTER they have been written.

INSERT INTO dbo.Spy(CheckID, ...)
SELECT C.CheckID, ... 
FROM dbo.CheckID  as I 
RIGHT OUTER JOIN dbo.Checks as C
  ON I.CheckID = C.CheckID
WHERE I.checkID IS NULL 
  AND I.Type = 'Fraud'

Upvotes: 1

bendataclear
bendataclear

Reputation: 3850

I always find the most stable operations are set based:

INSERT INTO MyTable
SELECT SomeValue
FROM Inserted
WHERE [logic here]

If it fails the logic it will just insert zero rows.

Upvotes: 1

Related Questions