How to track which record got update in table

Question

How to track which record got update in table

sami.sqldba

Ten Centuries

Points: 1248
More actions
November 11, 2011 at 6:29 am

#390836

Hi Friends
Here i have to create a trigger which update a column called lastmodifiedtime when ever there is change in that table,
now here i am not getting how to track which particular record got update so that i can update the column called lastmodfiedtime with getdate().
Thanking all in advance.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions.

Example:

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader

AFTER INSERT

AS

DECLARE @creditrating tinyint, @vendorid int;

IF EXISTS (SELECT *

FROM Purchasing.PurchaseOrderHeader p

JOIN inserted AS i

ON p.PurchaseOrderID = i.PurchaseOrderID

JOIN Purchasing.Vendor AS v

ON v.BusinessEntityID = p.VendorID

WHERE v.CreditRating = 5

)

BEGIN

RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1);

ROLLBACK TRANSACTION;

RETURN

END;

Using the inserted and deleted Tables

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

another example:

Create Trigger TableName_Insert On TableName For Insert As

Update Tb

Set tb.date_created = GetDate()

From TableName Tb

Join Inserted i on Tb.PKColumn = i.PKColumn

Go

Create Trigger TableName_Update On TableName For Update As

Update Tb

Set tb.date_modified = GetDate()

From TableName Tb

Join Inserted i on Tb.PKColumn = i.PKColumn

UPDATE mt

SET DateStamp = getdate()

FROM MyTable mt

JOIN Inserted i ON mt.ID = i.ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 3

Thanks Lowell and Dev

For your time and script... I am about to try this if i get into any trouble will get back for your suggestions 🙂

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 4

Dear Friends

I have tried dev code with some change to effect my code so here the trigger is

CREATE TRIGGER Trgr_Update_LastModifiedOn

ON SERVERINFO

AFTER UPDATE

AS

BEGIN

IF EXISTS(SELECT *

FROM SERVERINFO S

INNER JOIN INSERTED AS I ON I.INDEXS = S.INDEXS

)

BEGIN

UPDATE SERVERINFO SET LASTMODIFIEDON = GETDATE()

END

GO

but i am getting an error when i am tring to excute this trigger to create

as 'Incorrect syntax near the keyword 'AS''

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

Take out BEGIN & END after AS.

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

[ WITH <dml_trigger_option> [ ,...n ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 6

Thanks again dev but still i am facing a big problem when i am trying to update a single record tigger is updating getdate() for all the records in that table

for eg

UPDATE ServerInfo SET InternalValue = '2345'

WHERE Indexs = 5

now i want that lastmodifiedon should be effect only on record whoes indexs = 5 but trigger is updating to all records.

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

Lowell SSC Guru Points: 323518 More actions · Answer 7

NO.

your error is a logical one:

look at your trigger, it says this:

UPDATE SERVERINFO

SET LASTMODIFIEDON = GETDATE()

that says update EVERY ROW in the table to todays date.(if the exists function finds a match...with of course it does)

you have to use the examples, and see how they are using the special virtual table INSERTED to join on theoriginal table to affect only the rows that exist inside the triggers INSERTED table..that is...only the roiws with changes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

sami.sqldba (11/11/2011)
Thanks again dev but still i am facing a big problem when i am trying to update a single record tigger is updating getdate() for all the records in that table
for eg
UPDATE ServerInfo SET InternalValue = '2345'
WHERE Indexs = 5
now i want that lastmodifiedon should be effect only on record whoes indexs = 5 but trigger is updating to all records.

Because there is no filter condition in your where clause (in trigger definition) for precise record you want to update.

Lowell SSC Guru Points: 323518 More actions · Answer 9

there's no need to test an EXISTS either...if you are in the trigger, of course there are rows.

this will be what you are after i think:

CREATE TRIGGER Trgr_Update_LastModifiedOn

ON SERVERINFO

AFTER UPDATE

AS

BEGIN

UPDATE SERVERINFO

SET LASTMODIFIEDON = GETDATE()

FROM INSERTED AS I

WHERE I.INDEXS = SERVERINFO.INDEXS

END

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 10

Lowell (11/11/2011)
there's no need to test an EXISTS either...if you are in the trigger, of course there are rows.
this will be what you are after i think:
CREATE TRIGGER Trgr_Update_LastModifiedOn
ON SERVERINFO
AFTER UPDATE
AS
BEGIN
UPDATE SERVERINFO
SET LASTMODIFIEDON = GETDATE()
FROM INSERTED AS I
WHERE I.INDEXS = SERVERINFO.INDEXS
END
GO

ahm ahm...

There is no spoon, and there's no default ORDER BY in sql server either.

Lowell SSC Guru Points: 323518 More actions · Answer 11

dammit...tricked into doing homework again, is that what you are saying, my friend? 😀

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 12

Thanks a lot lot and lot and sorry for my foolishness as i am not in right mood to do work so getting completed worng but u both made me back in good track thanks you both 😀

Hats up to you both.

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com