user1837575 user1837575 - 4 months ago 8
SQL Question

Change value of inserted row's column with trigger

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

After insert, if a certain column = 'Fprice_bat', change the value of a different column 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
Comments