时间戳

首页 > 解决方案 > (TRIGGER) if cell1 = 'value' THEN INSERT INTO table (date) VALUES (CURRENT_TIMESTAMP)

sql-server - (TRIGGER) if cell1 = 'value' THEN INSERT INTO table (date) VALUES (CURRENT_TIMESTAMP)

问题描述

我需要在 SQL Server 中创建一个触发器，该触发器每次从列“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

我收到此错误消息

消息 207，级别 16，状态 1，过程 trg_FechaBaja，第 3 行 [批处理开始第 34 行]
列名“状态”无效。

     IF status = 'Baja' THEN BEGIN

在这一行中，“状态”给了我消息“无效的列名'状态'”，我 100% 确定我的列具有该名称。

IN RESUME：得到一个名为 table1 的表，其中有一列名为“status”，另一列名为“fechaBaja”

每次“状态”值更改为“Baja”时，我都需要触发并更新单元格“fechaBaja”current_timestamp

所有的操作都在同一个table1.

标签： sql-servertriggersdatabase-trigger

解决方案

首先，你想要一个update，而不是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

推荐阅读