I would like to achieve the following:
Users log into a web app, using their credentials from the
After inserting a row into the
inserttest table, a trigger activates in which it creates a new row in the
log_audit table, with one of the values as the
userid of the user who is currently logged in.
Is it possible to script this entirely in T-SQL? I can't seem to get my trigger to work properly.
Webapp connects via service account. It only uses one login in SQL Server (the one that is used in the connection string in the webconfig).
This is what I have:
CREATE TABLE inserttest ( id INT NOT NULL PRIMARY KEY IDENTITY, name VARCHAR(20) NULL, ) CREATE TABLE users ( id INT NOT NULL PRIMARY KEY IDENTITY, name VARCHAR(20) NULL ) CREATE TABLE log_audit ( logid INT PRIMARY KEY IDENTITY, userid INT NOT NULL, logdate DATE NULL ) CREATE TRIGGER tgr_insert ON inserttest AFTER UPDATE, INSERT AS BEGIN INSERT INTO log_audit (userid, logdate) VALUES (@USERIDofCURRENTAPPUSERinUSERSTABLE, GETDATE) END -- trigger script will not work of course