Click here to Skip to main content
15,897,187 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 2-Nov-17 2:15am

1 solution

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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900