读取 TSQL 触发器中插入的列名和值
我被要求为数据库中的每个表创建历史表。然后创建一个触发器,每当主表更新时,该触发器都会写入历史表。
历史表与主表具有相同的结构,但有一些额外的行(“id”和“更新类型”),
我以前从未对触发器做过任何事情,但我想做的是动态地遍历'Inserted' 中的列并构造一条插入语句来填充历史表。
但是我无法弄清楚如何读取列的名称及其各自的值。
我的半成品触发器目前看起来像......
CREATE TRIGGER tr_address_history
ON address
FOR UPDATE
AS
DECLARE @colCount int
DECLARE @maxCols int
SET @colCount = 0
SET @maxCols = (SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted')
PRINT 'Number of columns = ' + CONVERT(varChar(10),@maxCols)
WHILE (@colCount <= @maxCols)
BEGIN
DECLARE @name varchar(255)
SELECT @name = column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted'
DECLARE @value varchar(255)
SELECT @value = @name FROM Inserted
PRINT 'name = ' + @name + ' and value = ' + @value
SET @colCount = @colCount + 1
END
PRINT 'Done';
当触发器运行时,它只是说“列数= 0”
谁能告诉我出了什么问题:
SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted'
谢谢......
I've been asked to create history tables for every table in a database. Then create a trigger that will write to the history table whenever the primary table is updated.
The history tables have the same structure as the primary table, but with a couple of extra rows ('id' and 'update type')
I've never done anything with triggers before, but I would like to do is dynamically go through the columns in 'Inserted' and construct an insert statement to populate the history table.
However I cannot work out how to read the names of the columns and their individual values.
My half finished trigger currently looks like...
CREATE TRIGGER tr_address_history
ON address
FOR UPDATE
AS
DECLARE @colCount int
DECLARE @maxCols int
SET @colCount = 0
SET @maxCols = (SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted')
PRINT 'Number of columns = ' + CONVERT(varChar(10),@maxCols)
WHILE (@colCount <= @maxCols)
BEGIN
DECLARE @name varchar(255)
SELECT @name = column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted'
DECLARE @value varchar(255)
SELECT @value = @name FROM Inserted
PRINT 'name = ' + @name + ' and value = ' + @value
SET @colCount = @colCount + 1
END
PRINT 'Done';
When the trigger runs it just says "Number of columns = 0"
Can anyone tell me what's wrong with :
SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted'
Thanks...
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
Beenay25 提出的第一个解决方案很好,但您应该使用受影响的表而不是“插入”伪表。
这是:
而不是“INSERTED”
另外,您应该使用动态 SQL。
这将是一个完整的工作解决方案:
First solution proposed by Beenay25 is good, but you should use affected table instead of 'inserted' pseudotable.
This is:
Instead of 'INSERTED'
Also, you should use dynamic SQL.
This will be a complete working solution:
'inserted'
表是一个伪表;它不会出现在 INFORMATION_SCHEMA 中。触发器中使用
UPDATE()
运算符:COLUMNS_UPDATED
UPDATE()
The
'inserted'
table is a pseudo-table; it doesn't appear in INFORMATION_SCHEMA.There is the
UPDATE()
operator for use in triggers:COLUMNS_UPDATED
UPDATE()
有一种方法可以满足提问者的要求:
我在触发器中做了一些事情,测试特定表的所有列是否实际上参与了对该表的插入。如果他们这样做了,我后来将它们复制到历史表中。如果没有,则回滚并打印仅可以将完整的行插入到报告表中。也许他们可以根据自己的需求进行调整:
这里是:
然后您可以简单地执行 if @inserted_columncount < @actual_num_of_columns ..........
我这样做是因为我有一个 sp 每次运行时都会向报告表插入 1 个完整行。没关系,但我不想让其他人误触那张桌子。连我自己也不行。我也想保留历史。因此,我创建了这个触发器来保留历史记录,同时检查是否在没有报告表中所有列的值的情况下尝试插入,并进一步检查代码是否尝试更新或删除并回滚。
我正在考虑扩展它以允许更新,但其中设置了所有列。
这可以按如下方式完成:
如果尝试更新,
There is a way to do what the questioner requires:
I have made something inside a trigger that tests whether all the columns of a particular table actually participated in an insert to that table. If they did, I later copied them to a history table. If they did not, then rollback and print only complete rows may be inserted into the report table. Perhaps they could adapt this to their needs:
here it is:
Then you can simply do if @inserted_columncount < @actual_num_of_columns ..........
I did this because i have a sp that inserts 1 complete line to the report table every time it runs. That's fine, but i don't want anyone else touching that table by mistake. not even myself. I also want to keep history. So i made this trigger to keep the history but also to check if an insert was attempted without values for all the columns in the report table, and further down the code it checks if an update or delete was attempted and it rollbacks.
i was thinking of expanding this to allow an update but in which all the columns are set.
this could possibly be done as follows:
if update was attempted,