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)
 How to create After Update Trigger

Author  Topic 

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2007-12-26 : 03:22:59
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

52326 Posts

Posted - 2007-12-26 : 04:39:56
[code]CREATE TRIGGER Employee_Trg_Upd ON emplyeeORG
AFTER UPDATE
AS

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

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2007-12-26 : 06:29:55
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

52326 Posts

Posted - 2007-12-26 : 06:44:31
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 Post

Posted - 2008-01-14 : 15:53:53
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

2 Posts

Posted - 2009-07-23 : 07:11:02
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

8 Posts

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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-29 : 04:55:22
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
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-29 : 05:01:11
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 - 2009-12-29 : 05:11:46
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 - 2014-03-19 : 03:01:12
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
   

- Advertisement -