我们如何在SQL Server 2012中的触发器中使用会话值并插入表中? [英] How can we use session value in trigger in SQL server 2012 and insert into a table?
本文介绍了我们如何在SQL Server 2012中的触发器中使用会话值并插入表中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
How can we use session value in trigger in SQL Server 2012 and insert into a table? I created a session but getting confuse, how can i use session value into a trigger. I'm creating a log table for view all logs that which action is acted in main table. and please tell me how can I update table with update Trigger with new session value and ID fetch from main table to update data. In given code @ActionBy value I want from new session.
How can I do this? Please let me know about this.
我尝试过的事情:
What I have tried:
CREATE TRIGGER EpaperTrgrUpdate
ON EPaper
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EpaperId INT
DECLARE @Date datetime
DECLARE @ActionBy nvarchar(50)
DECLARE @Action varchar(50)
SELECT @EpaperId = EPaper.SrNo, @Date = date, @ActionBy = EPaper.ActionBy
FROM EPaper
IF UPDATE(IMG)
BEGIN
SET @Action = 'Image Updated'
END
IF UPDATE(Date)
BEGIN
SET @Action = 'Date Updated'
END
INSERT INTO Epaper_Log
VALUES(@EpaperId, @Date, @Action, @ActionBy)
END
推荐答案
你不能。您可以访问INSERTED和DELETED表,因此您必须先向其中一个表添加一个列并先设置它,然后您可以在触发器中读取它。
此外,写入触发器的方式不是编写触发器的好方法,因为它一次只能用于1条记录。如果您一次插入多个记录,则此触发器将不起作用。
实际上,这甚至不适用于单个记录,因为你没有限定你的WHERE子句,这意味着你将获得最后的记录取决于如何SQL读取表EPaper。
You can't. You do have access to the INSERTED and DELETED tables so you would have to add a column to one of the tables and set it first and then you could read it in the trigger.
Also, the way your trigger is written is not a good way to write triggers because it only works for 1 record at a time. If you ever insert more than one record at a time this trigger will not work.
In fact, this won't even work for a single record because you don't qualify your WHERE clause which means you'll get the last record depending on how SQL reads in the table EPaper.
这篇关于我们如何在SQL Server 2012中的触发器中使用会话值并插入表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文