如何从已删除或插入到TRIGGER中选择主键字段的值? [英] How to select the primary key field's value from deleted or inserted in a TRIGGER?

问题描述

我想创建一个TRIGGER，该触发器可用于数据库的每个表格.
因此，对于其他表，不会更改此TRIGGER的sqlstatement.
例如，
DB db_A有2个表格，tb_A和tb_B
我希望一个名为Loger的TRIGGER从已删除或已插入的表中获取主键字段的值.
当我在tb_A中插入新记录时，
TRIGGER Loger将获得插入表的主键字段的值.
但是我不知道主键字段的字段名.这是我的条件.
我可以在这种情况下创建此TRIGGER 吗?

I want to create a TRIGGER which can be used for every tabels of a database.
So this TRIGGER''s sqlstatement is not changed for a different table.
For example,
DB db_A have 2 tabels, tb_A and tb_B
I want a TRIGGER named Loger to get the primary key field''s value from deleted or inserted table.
when I insert a new record into tb_A,
the TRIGGER Loger will get the primary key field''s value of the inserted table.
but I don''t know the the primary key field''s fieldname .This is my condition.
Can I create this TRIGGER with this condition?

CREATE TRIGGER [TRIGGER_NAME] ON [TABLE]
FOR INSERT,UPDATE,DELETE
AS
    IF @@ROWCOUNT = 0
        RETURN ;
    IF NOT EXISTS(SELECT * FROM deleted)
       BEGIN
       --INSERT sqlstatement
        
       END

推荐答案

您不能为多个表创建单个触发器.您需要为每个表使用单独的触发器.因此，您可以创建一个存储过程，例如从触发器中使用该存储过程，并将主键名称以及一些其他信息传递给该过程.当然，您也可以使用系统表来获取主键名称，但是我认为这没有道理.

You cannot create a single trigger for several tables. You need to have separate triggers for each table. Because of this you can create for example a stored procedure which you use from the trigger and pass the primary key name along some other information to the procedure. Of course you can also use system tables to fetch the primary key name, but I don''t think that would make sense.

这篇关于如何从已删除或插入到TRIGGER中选择主键字段的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！