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)
 How to create After Update Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 12/26/2007 :  03:22:59  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
i need to create a trigger so i can get the orginal values in a separate table to track changes, both table r same in column structre

how can i create a trigger After Update


table Orignal name: emplyeeORG
table Track name: employeeTRK

ID FirstName LastName
1 abc xyz
2 fgh ijk

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/26/2007 :  04:39:56  Show Profile  Reply with Quote
CREATE TRIGGER Employee_Trg_Upd ON emplyeeORG
AFTER UPDATE 
AS

INSERT INTO employeeTRK
SELECT ID,FirstName,LastName FROM DELETED
GO
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 12/26/2007 :  06:29:55  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
Thanx a lot dear...

actually i was using FROM UPDATED (and that was my mistake)

thanx again
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/26/2007 :  06:44:31  Show Profile  Reply with Quote
There is no internal table called UPDATED. The update is actually handled internally as a DELETE followed by INSERT so that you will get old values in DELETED and new values in INSERTED table after an update operation
Go to Top of Page

pzn3xq
Starting Member

1 Posts

Posted - 01/14/2008 :  15:53:53  Show Profile  Reply with Quote
Hi,
I have two questions.

1) Is there a way to automatically determine which field changed from the orginal table? Lets you want to capture just first name, or last name rather than all fields.

2) Is there a way to take the single field changed from above, and insert that into your employeeTRK table?

Thanks,
John
Go to Top of Page

unique_rehan
Starting Member

Pakistan
2 Posts

Posted - 07/23/2009 :  07:11:02  Show Profile  Reply with Quote
i have two different databases and udpate two table from one of each database as insert a value in a data base's table than other updated its data automatically
Go to Top of Page

swatip
Starting Member

India
8 Posts

Posted - 12/29/2009 :  04:47:44  Show Profile  Reply with Quote
I didn't understand the UPDATED and INSERTED tables concept.Anyone can please help?
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 12/29/2009 :  04:55:22  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
There are two special virtual tables in MSsql server

1.Inserted
2.Deleted

These tables are affected as below when a DML Statement hits your table.

1.Insert statement -> New record inserted -> Insert table
2.Update Statement -> Old record is deleted and new record is inserted -> Deleted,Inserted Tables
3.Delete statement ->Old record is deleted ->Deleted table





Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 12/29/2009 :  05:01:11  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
quote:
Originally posted by swatip

I didn't understand the UPDATED and INSERTED tables concept.Anyone can please help?



Need much?

Go through this..

http://shujaatsiddiqi.blogspot.com/2008/04/inserted-deleted-tables-in-sql-server.html

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/
Go to Top of Page

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 12/29/2009 :  05:11:46  Show Profile  Send rajdaksha a Yahoo! Message  Reply with Quote
Hi swatip

More clear refer this..

http://msdn.microsoft.com/en-us/library/ms191300.aspx

-------------------------
R...
Go to Top of Page

aaabbb
Starting Member

2 Posts

Posted - 03/19/2014 :  03:01:12  Show Profile  Reply with Quote
Create triggers and assertions for Bank database handling deposits and loan and admission database handling seat allocation and vacancy position. Design the above relational database schema and implement the following triggers and assertions.
a. When a deposit is made by a customer, create a trigger for updating customers account and bank account
b. When a loan is issued to the customer, create a trigger for updating
customer’s loan account and bank account.
c. Create assertion for bank database so that the total loan amount does not exceed the total balance in the bank.
d. When an admission is made, create a trigger for updating the seat allocation details and vacancy position.
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.09 seconds. Powered By: Snitz Forums 2000