July 19, 2010 at 1:55 am
I've tried to make a trigger to put a timestamp record into a column on update, delete and insert. I've got so far that when I inserted a new row the trigger created a new row with the timestamp record but left all the other columns empty.
Insert statement makes 2 rows into a table. The first row is the inserted data and the second is made by the trigger with the timestamp.
How can I get the timestamp on the same row as the created row.
Let the columns be id, 1, 2, 3, modified_date
Yours
Toni
July 19, 2010 at 2:02 am
Please post table definition and your current trigger.
My guess is that you're inserting within the trigger. That'll create a new row. What you rather need to do (assuming this is an AFTER trigger, not an INSTEAD OF) is update the row just inserted. You can identify the just inserted row(s) with the inserted table, so you can do a simple UPDATE table WHERE EXISTS ....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2010 at 3:10 am
CREATE TRIGGER RecordUpdate
ON Rooms
AFTER INSERT
AS
BEGIN
INSERT INTO DatabaseOperations
VALUES(N'', N'', N'',
N'', GETDATE())
END
GO
this is the thing what i've tried. Got this example from somewhere, and i've tried to edit it
in many ways.
July 19, 2010 at 3:14 am
What you're saying in that trigger is once a row has been inserted, please insert a second row with blank values. INSERT always adds a new row.
If you want to update the row just inserted, you'll need an update statement in the trigger, not an insert. Do you know how to write an update statement?
Please post the table definition. It's hard to write working triggers if one doesn't know what the table the trigger is on looks like.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2010 at 3:20 am
ID int IDENTITY(1,1) NOT NULL, <-- primary key
name varchar(100) NULL,
type varchar(2) NULL,
address char(11) NULL,
start_date [int] NULL,
end_date [int] NULL,
modified_date datetime NULL, <-- this is where the insert, update, delete timestamps suppose to go.
thnx
T
July 19, 2010 at 3:30 am
Do I assume that the answer to 'Do you know how to write an update statement' is No?
It's easy enough to set the modified date after an insert or update. Delete however removes the row, so there's nothing left to put a date against. If you want to audit deletes, you need another way, typically an audit table.
Your trigger is on the Rooms table, but the insert in there inserts a row into the DatabaseOperations table.
Do you want audit rows in the DatabaseOperations table whenever someone inserts, updates or deletes a row from rooms?
Do you want a column in the rooms table setting to the modified date?
Do you want something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2010 at 3:41 am
toni.harju (7/19/2010)
modified_date datetime NULL, <-- this is where the insert, update, delete timestamps suppose to go.
Puzzling. I can see how you might want to record the date that a record was inserted or updated, but deletes?
Surely the row is deleted by a delete? There would be no modified_date value left to modify...!
Seeing as this is the SQL Server 2008 section of the site, why bother creating your own audit trail with triggers?
SQL Server provides built-in audit features including Change Tracking and Change Data Capture. Please see the following link (in Books Online) for details:
(follow the links from that page)
July 19, 2010 at 3:46 am
Yes, No I mean no to the update statement =)
Yep, that's what I was thinking too about the deleted rows.
There's an error with the post of the table name, it suppose to be the same in both tables. I'm only working with one table for now.
T
July 19, 2010 at 3:54 am
For starters...
CREATE TRIGGER RecordUpdate
ON Rooms
AFTER INSERT, UPDATE -- catch new inserts and modifications
AS
BEGIN
UPDATE Rooms
SET modified_date = getdate() -- modified now
WHERE ID IN (SELECT ID FROM inserted) -- where the row was affected by the insert/update that fired this trigger
END
GO
This isn't particularly good auditing. All it tells you is the last time someone modified the row. You don't know who modified it or what they did. If you do need that, or to audit deletes, you're going to need something a little more comprehensive, whether that be custom-written or using CDC or Change Tracking (assuming you are using SQL 2008)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2010 at 4:14 am
Thank You!
I'm just doing what these people tell me to do here = )
But we're doing these in couple different ways to know which is the best way
to record things up in the database.
I think that we're doing this triggering in some tables and more intense record keeping
of some tables. Just like you said that it's hard to know who did and what.
But I think you'll hear from me soon 😉
Thanks again
T
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply