标记应在SQL Server中触发之前或之后使用

I have a column SLTag, data type is bit, and another column SLFile with a data type of VARBINARY(MAX).

When web application is calling to update this table, my trigger shall update SLTag = 1 when SLFile has data, reversely, update SLTag = 0 when SLFile is emptied.

    /*
        Purpose: Trigger update SLTag to 1 when SLFile is not null
    */

    CREATE TRIGGER [SLTag_UPDATE] 
    ON [Employee] 
    AFTER UPDATE
    AS
        DECLARE @EMPLOYEEID AS INT;
    BEGIN
        SET NOCOUNT ON;

        SELECT @EMPLOYEEID = EmployeeId 
        FROM inserted

        UPDATE EMPLOYEE 
        SET SLTag = CASE 
                       WHEN LEN(SLFile) > 0 THEN 1
                       ELSE 0
                    END
        WHERE EmployeeId = @EMPLOYEEID
    END
    GO

    ALTER TABLE [dbo].[Employee] ENABLE TRIGGER [SLTag_UPDATE]
    GO

My question here is: is it appropriate to write the trigger this way? Or I shall write the trigger using instead of?

评论
  • 无言以对
    无言以对 回复

    为什么还要烦恼触发器?您可以轻松地创建一个包含该信息的计算列,无需手动干预即可保持“最新”状态:

    ALTER TABLE dbo.Employee
    ADD IsEmpty AS CASE WHEN DATALENGTH(SLFile) = 0 THEN 1 WHEN SLFile IS NULL THEN 1 ELSE 0 END PERSISTED
    

    So if SLFile is NULL or has a DATALENGTH of 0 - then IsEmpty will show a value of 1 (true) - otherwise 0 (false).

    无需混乱的触发器,无需不断更新-SQL Server将在幕后自动为您解决所有问题