How to alter inserted row in sql server trigger

Question

This gave an error

Msg 156, Level 15, State 1, Procedure Trig_Insert_Serials_Null, Line 30
Incorrect syntax near the keyword 'INSERT'.

Table structure:

Serials (CurrencyId, DivisionId, BranchId, NewSerialNumber, Display, TypeId)

I want to change 0 to null in (CurrencyId, DivisionId, BranchId).

CREATE TRIGGER Trig_Insert_Serials_Null
   ON  Serials
   INSTEAD OF INSERT   
AS 
BEGIN

DECLARE @currencyId int;
DECLARE @branchId int;
DECLARE @divisionId int;

SELECT @currencyId = INSERTED.CurrenceyId FROM INSERTED;
SELECT @branchId = INSERTED.BranchId FROM INSERTED;
SELECT @divisionId = INSERTED.DivisionId FROM INSERTED;

IF @currencyId = 0 
    SET @currencyId = NULL;
END

IF @branchId = 0 
    SET @branchId = NULL;
END

IF @divisionId = 0 
    SET @divisionId = NULL;
END


INSERT INTO Serials (CurrenceyId,DivisionId,BranchId,NewSerialNumber,
                         Display, TypeId)
VALUES (INSERTED.CurrenceyId,INSERTED.DivisionId, INSERTED.BranchId,
         INSERTED.NewSerialNumber, INSERTED.Display,INSERTED.TypeId)

END
GO

marc_s · Accepted Answer · 2012-10-17 11:45:56Z

You are assuming that the trigger is fired once per row - this is wrong.

The trigger is fired once per batch and could be fired for a single INSERT statement that insert 50 rows.

Therefore, your statements like this:

SELECT @currencyId = INSERTED.CurrenceyId FROM INSERTED;

will fail miserably.

You need to re-write your trigger to take this into account - the Inserted table can contain one or multiple rows - you cannot just plainly assume it's always a single row.

Basically, you need to do something like this:

 INSERT INTO Serials (CurrenceyId, DivisionId, BranchId, NewSerialNumber, Display, TypeId)
    SELECT
       CASE i.CurrenceyId WHEN 0 THEN NULL ELSE i.CurrenceyId END,
       CASE i.DivisionId WHEN 0 THEN NULL ELSE i.DivisionId END,
       CASE i.BranchId WHEN 0 THEN NULL ELSE i.BranchId END,
       i.NewSerialNumber, i.Display, i.TypeId
    FROM Inserted i

Joe G Joseph · Accepted Answer · 2012-10-17 11:43:09Z

1

Remove 3 END s that you have with your 3 IF statements.

END should Start with a BEGIN

You have 4 END s and Only one BEGIN in your trigger

answered Oct 17, 2012 at 11:43

Joe G Joseph

24.2k5 gold badges58 silver badges59 bronze badges

1 Comment

Alaa Jabre Over a year ago

sorry buddy @marc_s answer is better.

Collectives™ on Stack Overflow

How to alter inserted row in sql server trigger

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related