Insert before update trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert before update trigger

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-29 : 01:55:56

Dear Member

i have two table ,in one table write a trigger of update a rows to insert updated record in second table with update flag
I want to first insert a current rows and then
insert updated record in second table
which trigger i can write ?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2013-05-29 : 02:02:30

Go for After trigger.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-29 : 02:36:55

After trigger insert only updated record i want first insert before updated record and then updated record

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 02:44:41

quote:
Originally posted by amirs

After trigger insert only updated record i want first insert before updated record and then updated record

The firset insert will be done through a procedure or through adhoc query. Then trigger will take care of the update part.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-29 : 03:07:33

Please send me any example

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 03:57:32

Post your table structure and some sample data you need to insert with final output you need to see in the tables and we will help with query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-29 : 04:44:35

table1
col1 col2 col3 col5
item1 size1 qty1 price1
after update
item1 size1 newqty newpric

item2 size2 qty2 price2
item3 size3 qty3 price3
item4 size4 qty4 price4

table2
col1 col2 col3 col5 flag col7
item1 size1 qty1 price1 oldval datetime
item2 size2 newqty newprc updates datetime
item3 size3 qty3 price3 oldval datetime
item4 size4 newqty newpric updated datetime

I have update a row in table1 at this time insert old and updated record in table2 means i have one record update in table1 and two record are insert in table2 is old and updated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 05:04:33

You need to write a procedure for doing the update like


CREATE PROC ModifyValues
@qty int,
@price decimal(10,2),
@col1 int
AS
UPDATE yourTable
SET col5 = @price,
col4 = @qty
WHERE col1=@col1
GO

and a trigger to capture the changed values as


CREATE TRIGGER GetDataUpdates
ON YourTable
FOR UPDATE
AS
BEGIN
INSERT INTO table2
SELECT col1, col2, col3, col5,'oldval',GETDATE()
FROM DELETED
UNION ALL
SELECT col1, col2, col3, col5,'updates',GETDATE()
FROM INSERTED
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-29 : 05:28:16

Thanks visakh16 for your valuable help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 05:46:53

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources