Nsryjdtyk

I need to create a trigger in SQL Server that triggers every time that the value from column "status" = 'Baja'

CREATE TRIGGER trg_triggerName 

ON dbo.table1 

AFTER UPDATE 

AS

BEGIN

    IF status = 'Baja' THEN BEGIN

        INSERT INTO dbo.table1 (fechaBaja)  

        VALUES (CURRENT_TIMESTAMP) 

    END

END

GO

I got this error message

Msg 207, Level 16, State 1, Procedure trg_FechaBaja, Line 3 [Batch Start Line 34]

Invalid column name 'status'.

     IF status = 'Baja' THEN BEGIN

In this line the "status" give me the message "invalid column name 'status'" and I'm 100% sure that my column has that name.

IN RESUME: Got a table named table1 that has a column named 'status' and another column named 'fechaBaja'

Every time that the 'status' value changes to 'Baja', I need to trigger and update the cell 'fechaBaja' with the current_timestamp

All the operations are in the same table1.

I believe you want to update fechaBaja field if updated row status field equal'Baja'. You need to capture primary key information as well. I put 'Id' but you need to change it with your actuel field name.

 CREATE TRIGGER trg_triggerName ON dbo.table1 

 AFTER UPDATE AS

 BEGIN



        UPDATE dbo.table1 SET fechaBaja=CURRENT_TIMESTAMP 

        WHERE EXISTS (SELECT 1 FROM INSERTED I WHERE dbo.table1.Id = I.Id AND I.Status='Baja')



 END

 GO

First, you want an update, not an insert.

Second, A trigger in SQL Server is fired once per statement, not once per row. This means that if the update statement that fired the trigger have updated multiple rows, your trigger will be fired once, and include data about these rows in the inserted and deleted tables.

Third, You need to make sure that the update statement inside the trigger will not raise it again. Do that by configuring the database.

The code you need is something like this:

 CREATE TRIGGER trg_triggerName ON dbo.table1 

     AFTER UPDATE AS

 BEGIN



     UPDATE t

     SET fechaBaja = CURRENT_TIMESTAMP

     FROM dbo.table1 As T

     INNER JOIN Inserted As I

         ON T.<PrimaryKey> = I.<PrimaryKey>

     INNER JOIN Deleted As D

         ON T.<PrimaryKey> = D.<PrimaryKey>

     WHERE I.[status] = 'Baja'

     AND (D.[Status] IS NULL OR D.[Status] <> 'Baja')



 END

 GO

You can do it by joining the three tables like

CREATE TRIGGER trg_triggerName ON dbo.table1 AFTER UPDATE AS

 BEGIN

     UPDATE T1

     SET T1.fechaBaja = CURRENT_TIMESTAMP

     FROM dbo.table1 T1 INNER JOIN INSERTED T2 ON T1.ID = T2.ID

                        INNER JOIN DELETED  T3 ON T1.ID = T3.ID

     WHERE T2.[status] = 'Baja'

           AND

           (T3.[status] <> 'Baja' OR T3.[status] IS NULL);

 END

 GO

• There is no need to use IF.


• 
  status is a reserved word, enclose it in brackets.


• Join your table with INSERTED table to get all the IDs of the rows updated.


• Join your table with DELETED table to get all the IDs where the data is not 'Baja'.


• Filter by where INSERTED.[status] = 'Baja' AND DELETED.[status] <> 'Baja' to update only those rows.