I'd like to copy a table's row before updating and I'm trying to do it like this:
我想在更新之前复制一个表的行,我试图这样做:
CREATE TRIGGER first_trigger_test
on Triggertest
FOR UPDATE
AS
insert into Triggertest select * from Inserted
Unfortunately, I get the error message
不幸的是,我收到了错误消息
Msg 8101, Level 16, State 1, Procedure first_trigger_test, Line 6
An explicit value for the identity column in table 'Triggertest' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I assume it's because of the id-column; can't I do something like 'except' id? I do not want to list all the columns in the trigger as it should be as dynamic as possible...
我认为这是因为id-column;我不能做'除'id之类的事情吗?我不想列出触发器中的所有列,因为它应尽可能动态...
3 个解决方案
#1
You can't, basically. You'll either have to specify the columns, or use a separate table:
基本上你不能。您要么必须指定列,要么使用单独的表:
CREATE TRIGGER first_trigger_test
on Triggertest
FOR UPDATE
AS
insert into Triggertest_audit select * from deleted
(where Triggertest_audit is a second table that looks like Triggertest, but without the primary key/identity/etc - commonly multiple rows per logical source row; not I assumed you actually wanted to copy the old values, not the new ones)
(其中Triggertest_audit是第二个看起来像Triggertest的表,但没有主键/身份/等 - 每个逻辑源行通常有多行;不是我认为你实际上想要复制旧值,而不是新值)
#2
The problem happens because you are trying to set an identity column in Triggertest. Is that your plan?
出现此问题的原因是您尝试在Triggertest中设置标识列。那是你的计划吗?
-
If you want to copy the new identity columns from INSERTED into Triggertest, then define the column in Triggertest without IDENTITY
如果要将新标识列从INSERTED复制到Triggertest,则在没有IDENTITY的情况下在Triggertest中定义列
-
If Triggertest has it's own IDENTITY columns, use this:
如果Triggertest有自己的IDENTITY列,请使用:
insert into Triggertest (col1, col2, col3) select col1, col2, col3 from Inserted
插入到Triggertest(col1,col2,col3)中,从Inserted中选择col1,col2,col3
After comment:
No, you can't without dynamic SQL to detect what table and find all non-identity colums.
不,你不能没有动态SQL来检测什么表并找到所有非身份列。
However, if you add or remove columns you'll then have a mis-match between trigger table and Triggertest and you'll get a different error.
但是,如果添加或删除列,则触发器表和Triggertest之间会出现不匹配,您将收到不同的错误。
If you really want it that dynamic, you'd have to concat all columns into one or use XML to ignore schema.
如果你真的希望它是动态的,你必须将所有列连接成一个或使用XML来忽略模式。
Finally:
Do all your tables have exactly the same number of columns and datatypes and nullability as TriggerTest... because this is the assumption here...
您的所有表都具有与TriggerTest完全相同的列数和数据类型以及可空性...因为这是假设...
#3
IF you want the table to be built each time the trigger runs then you have no choice but to use the the system tables to find the columns and create a table with those column definitions. Of course your first step will have to be to drop the existing table or the trigger won't work the second time someone updates a record.
如果您希望每次触发器运行时都构建表,那么您别无选择,只能使用系统表查找列并创建包含这些列定义的表。当然,您的第一步必须是删除现有表,否则第二次有人更新记录时触发器将无法工作。
However, I think you need to rethink this process. Dropping a table then creating a new one every time you change a record is a seriously bad idea. How is this table in anyway useful when it may get wiped out and rebuilt every second or so?
但是,我认为你需要重新考虑这个过程。每次更改记录时删除一个表然后创建一个新表是一个非常糟糕的主意。无论如何,当这张桌子每隔一秒左右被消灭并重建时,它是如何有用的?
What you might consider doing instead is create a dynamic process to create the Create trigger scripts that have the correct information for that table but which are not dynamic. Then your configuration people need to run this process every time table changes are made.
您可能会考虑做的是创建一个动态过程来创建创建触发器脚本,该脚本具有该表的正确信息但不是动态的。然后,每次进行表更改时,您的配置人员都需要运行此过程。
Remember it is critical for triggers to do two things, run as fast as humanly possible and account for proccesing all the records inthe batch (triggers should never have row-by-row proccessing or other slow processses or assume only one row will be in inserted or deleted tables) Dynamic SQL in a trigger is porbably also a bad idea as you can't test out all the possibilites beforehand and can bring your whole production server to a screaming halt when some unexpected thing happens.
请记住,触发器执行两项操作至关重要,尽可能快地运行,并考虑在批处理中处理所有记录(触发器应该永远不会有逐行处理或其他缓慢处理或假设只插入一行或删除表格)触发器中的动态SQL也可能是一个坏主意,因为您无法预先测试所有可能性,并且可能会在发生意外情况时将整个生产服务器置于尖叫状态。
#1
You can't, basically. You'll either have to specify the columns, or use a separate table:
基本上你不能。您要么必须指定列,要么使用单独的表:
CREATE TRIGGER first_trigger_test
on Triggertest
FOR UPDATE
AS
insert into Triggertest_audit select * from deleted
(where Triggertest_audit is a second table that looks like Triggertest, but without the primary key/identity/etc - commonly multiple rows per logical source row; not I assumed you actually wanted to copy the old values, not the new ones)
(其中Triggertest_audit是第二个看起来像Triggertest的表,但没有主键/身份/等 - 每个逻辑源行通常有多行;不是我认为你实际上想要复制旧值,而不是新值)
#2
The problem happens because you are trying to set an identity column in Triggertest. Is that your plan?
出现此问题的原因是您尝试在Triggertest中设置标识列。那是你的计划吗?
-
If you want to copy the new identity columns from INSERTED into Triggertest, then define the column in Triggertest without IDENTITY
如果要将新标识列从INSERTED复制到Triggertest,则在没有IDENTITY的情况下在Triggertest中定义列
-
If Triggertest has it's own IDENTITY columns, use this:
如果Triggertest有自己的IDENTITY列,请使用:
insert into Triggertest (col1, col2, col3) select col1, col2, col3 from Inserted
插入到Triggertest(col1,col2,col3)中,从Inserted中选择col1,col2,col3
After comment:
No, you can't without dynamic SQL to detect what table and find all non-identity colums.
不,你不能没有动态SQL来检测什么表并找到所有非身份列。
However, if you add or remove columns you'll then have a mis-match between trigger table and Triggertest and you'll get a different error.
但是,如果添加或删除列,则触发器表和Triggertest之间会出现不匹配,您将收到不同的错误。
If you really want it that dynamic, you'd have to concat all columns into one or use XML to ignore schema.
如果你真的希望它是动态的,你必须将所有列连接成一个或使用XML来忽略模式。
Finally:
Do all your tables have exactly the same number of columns and datatypes and nullability as TriggerTest... because this is the assumption here...
您的所有表都具有与TriggerTest完全相同的列数和数据类型以及可空性...因为这是假设...
#3
IF you want the table to be built each time the trigger runs then you have no choice but to use the the system tables to find the columns and create a table with those column definitions. Of course your first step will have to be to drop the existing table or the trigger won't work the second time someone updates a record.
如果您希望每次触发器运行时都构建表,那么您别无选择,只能使用系统表查找列并创建包含这些列定义的表。当然,您的第一步必须是删除现有表,否则第二次有人更新记录时触发器将无法工作。
However, I think you need to rethink this process. Dropping a table then creating a new one every time you change a record is a seriously bad idea. How is this table in anyway useful when it may get wiped out and rebuilt every second or so?
但是,我认为你需要重新考虑这个过程。每次更改记录时删除一个表然后创建一个新表是一个非常糟糕的主意。无论如何,当这张桌子每隔一秒左右被消灭并重建时,它是如何有用的?
What you might consider doing instead is create a dynamic process to create the Create trigger scripts that have the correct information for that table but which are not dynamic. Then your configuration people need to run this process every time table changes are made.
您可能会考虑做的是创建一个动态过程来创建创建触发器脚本,该脚本具有该表的正确信息但不是动态的。然后,每次进行表更改时,您的配置人员都需要运行此过程。
Remember it is critical for triggers to do two things, run as fast as humanly possible and account for proccesing all the records inthe batch (triggers should never have row-by-row proccessing or other slow processses or assume only one row will be in inserted or deleted tables) Dynamic SQL in a trigger is porbably also a bad idea as you can't test out all the possibilites beforehand and can bring your whole production server to a screaming halt when some unexpected thing happens.
请记住,触发器执行两项操作至关重要,尽可能快地运行,并考虑在批处理中处理所有记录(触发器应该永远不会有逐行处理或其他缓慢处理或假设只插入一行或删除表格)触发器中的动态SQL也可能是一个坏主意,因为您无法预先测试所有可能性,并且可能会在发生意外情况时将整个生产服务器置于尖叫状态。