SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Insert before update trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amirs
Constraint Violating Yak Guru

India
259 Posts

Posted - 05/29/2013 :  01:55:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 05/29/2013 :  02:02:30  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Go for After trigger.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

amirs
Constraint Violating Yak Guru

India
259 Posts

Posted - 05/29/2013 :  02:36:55  Show Profile  Reply with Quote
After trigger insert only updated record i want first insert before updated record and then updated record
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/29/2013 :  02:44:41  Show Profile  Reply with Quote
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
Go to Top of Page

amirs
Constraint Violating Yak Guru

India
259 Posts

Posted - 05/29/2013 :  03:07:33  Show Profile  Reply with Quote
Please send me any example
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/29/2013 :  03:57:32  Show Profile  Reply with Quote
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
Go to Top of Page

amirs
Constraint Violating Yak Guru

India
259 Posts

Posted - 05/29/2013 :  04:44:35  Show Profile  Reply with Quote


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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/29/2013 :  05:04:33  Show Profile  Reply with Quote
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
Go to Top of Page

amirs
Constraint Violating Yak Guru

India
259 Posts

Posted - 05/29/2013 :  05:28:16  Show Profile  Reply with Quote
Thanks visakh16 for your valuable help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/29/2013 :  05:46:53  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000