user1837575 user1837575 - 4 months ago 8
SQL Question

Change value of inserted row's field with trigger

I'm trying to write a trigger with this logic:

After insert, if a certain field = 'Fprice_bat' change the value of a different field to '0000'

What I have written so far tries to simply add a new record to the table instead of updating the inserted line.

My query looks like this right now.

CREATE TRIGGER [dbo].[TR_FixBatchTimes_I]
ON [dbo].[HEADER_BAT]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@Starttime varchar(6),
@Endtime varchar(6),
@Batchstate varchar(30),
@Batchtype varchar(30)
SELECT
@Starttime = F908,
@Endtime = F910,
@Batchstate = F914,
@Batchtype = F916
FROM inserted
IF @Batchtype = 'FSPRICE_BAT'
BEGIN
INSERT INTO HEADER_BAT
(F908, F910, F914)
VALUES (0000, 2359, 'WAIT' )
END


I imagine my mistake is because I'm calling "Insert into header_Bat" and it should say something along the lines of insert into inserted... but that syntax isn't right.

How do I make this query edit the inserted line instead of add a new record to the table?

Answer

Your first mistake is to assume single row insert only. All DML operations are set operations. So if you want to update something work with sets.

CREATE TRIGGER [dbo].[TR_FixBatchTimes_I]
    ON  [dbo].[HEADER_BAT]
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    update HEADER_BAT
      set F908='0000', F910=2359, F914='WAIT'
    from HEADER_BAT h
         inner join inserted i on h.id = i.id --or what is PK
    where i.F916 = 'FSPRICE_BAT'
END