SQL Server Trigger after Update Record

Question

Hey there so I'm trying to create a trigger in SQL Server to automatic insert getdate() and SYSTEM_USER in a column after a record has been updated instead of including in a Update Query.

DB: Company

Table:  dbo.Lincensee

Column: DateModified -> I want to Insert: getdate()
Column: ModifiedBy - > I want to insert: SYSTEM_USER

After a specific Record has been updated from a Query Running in C#.

Query for example:

string strQuery = @"UPDATE dbo.Licensee SET LNAME = @LastName, 
                                FNAME = @FirstName,
                                MIDNAME = @MiddleName,
                                FIRMNAME = @OrganizationName,
                                SSN = @SSN,
                                WHERE LICNUM = @licenseToUpdate";
SqlCommand updateCommand = new SqlCommand(strQuery, connUpdateLicense);
...

PS: I can Hard code it inside the Set query, but I rather have it in a Trigger.

Any Ideas? Thank you.

What have you tried so far and what error did you get? Hint: You want an AFTER UPDATE trigger http://stackoverflow.com/questions/25568526/sql-server-after-update-trigger — Nick.Mc, Mar 03 '17 at 00:27
Possible duplicate of [SQL Server after update trigger](http://stackoverflow.com/questions/25568526/sql-server-after-update-trigger) — Nick.Mc, Mar 03 '17 at 00:27
From Suresh: Try this link,and you will find your answer: [AFTER UPDATE TRIGGERS](http://www.tutorialgateway.org/after-update-triggers-in-sql-server/) — Panagiotis Kanavos, Mar 07 '17 at 12:52

score 0 · Accepted Answer · answered Mar 09 '17 at 22:06

I followed Suresh Link and Found the T-SQL Code using the After Update. And Modified with my database:

create trigger [INS].[licpro_modby_dateby]
on [INS].[LICPRO]
after insert, update
as
begin
update a
set modifiedby = system_user,
    datemodified = getdate()
from ins.licpro as a
join inserted as b 
on a.licnum = b.licnum;

Thank you guys, sorry for the late reply I was off.

SQL Server Trigger after Update Record

1 Answers1