How to write trigger for before update the table value in SQL server 2005

Question

0.00/5 (No votes)

See more:

I'm using Sql Server 2005.I need to write Trigger for sql table before Update the table.i've two tables one is tblEmpDetail and Another one is tblEmpHistoryDetail .
The table has fields
i)tblEmpDetail :-
ID numeric(18) AutoIncrement,
EmpName nvarchar(50),
Dob datetime
ii)tblEmpHistoryDetail

SQL

ID numeric(18) AutoIncrement,
MasterID numeric(18),
EmpName nvarchar(20),
Dob datetime

how to create Trigger for before an update

What I have tried:

My Trigger code is

SQL

CREATE TRIGGER trgrBeforeUpdate on tblEmpDetail 
For Update
 declare @MasterID numeric(18)
 declare @EmpName nvarchar(50);
 declare @Dob Datetime;

select @MasterID=i.ID from inserted i;
select @EmpName =i.EmpName from inserted i;
select @Dob =i.Dob from inserted i;
	INSERT INTO tblEmpHistoryDetail 
         (MasterID,EmpName,Dob)
        Values
        (@MasterID,@EmpName,@Dob)
	PRINT 'BEFORE UPDATE trigger fired.'

Posted 18-Mar-19 2:06am

Flower@12

Updated 21-Mar-19 7:01am

Herman<T>.Instance

v5

Add a Solution

Comments

Santosh kumar Pithani 18-Mar-19 9:21am

There is no need to use triggers instead use output clau..

CREATE TRIGGER trgrBeforeUpdate on tblEmpDetail
For Update
AS
---Newly inserted value
INSERT INTO
tblEmpHistoryDetail (MasterID,EmpName,Dob)
select ID,EmpName,Dob from inserted;
---Deleted Old value
INSERT INTO
tblEmpHistoryDetail (MasterID,EmpName,Dob)
select ID,EmpName,Dob from deleted;

CHill60 18-Mar-19 9:38am

OP has clearly stated this is for SQL 2005. The output clause was introduced with SQL 2008 - OUTPUT Clause (Transact-SQL) - SQL Server | Microsoft Docs[^]

Santosh kumar Pithani 18-Mar-19 9:42am

Yes chill60,your right i will update my solution..Thank u so much

CHill60 18-Mar-19 9:40am

You can't. What are you actually trying to achieve? You can compare the deleted and inserted tables in the (after) Update Trigger if you want to know what has changed. But the code you currently have would require the after-update trigger anyway

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2019-03-21T07:02:00

When writing a trigger, you need to remember that the inserted and updated virtual tables could contain multiple rows. As it stands, your trigger will only log the details of the last row updated by each UPDATE statement.

Common SQL Server Mistakes – Multi Row DML Triggers - SQLServerCentral[^]

Based on your description, I suspect you want to log the old column values from your trigger. You don't need the trigger to run before the UPDATE statement to do that; you just need to use the deleted virtual table instead.

Use the inserted and deleted Tables - SQL Server | Microsoft Docs[^]
(That documentation says "Starting with SQL 2008", but that's wrong. The information applies from at least SQL 2000; it's just that 2008 is the earliest version that's still supported.)

SQL

CREATE TRIGGER trgrBeforeUpdate 
ON tblEmpDetail 
AFTER UPDATE
As
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO tblEmpHistoryDetail (MasterID, EmpName, Dob)
    SELECT MasterID, EmpName, Dob
    FROM deleted;
END;

If you really want a trigger that fires before the table is updated, then you would need to use an INSTEAD OF trigger[^]. The trigger would then be responsible for updating the source table as well.

It's worth noting that support for SQL 2005 ended in April 2016, and support for SQL 2008 and 2008 R2 will end in July. If at all possible, you should be looking to upgrade to a supported version - especially as the versions of Windows that 2005/2008/2008R2 run on will probably be out of support as well.