I'm making a simple table with names, emails etc, but I also have a ModifiedDate
. My idea is to use a trigger after both insert and update, and insert the current date. Thus if anyone does anything (except delete) to that column, the date should reflect that.
This is however not working.
CREATE TRIGGER ModDate
ON X
AFTER INSERT, UPDATE
AS
BEGIN
INSERT INTO X (ModifiedDate)
VALUES (GETDATE())
END
Now I have a couple of values that can't be null, and what this seems to do is try and create a new row. I would like it to insert the date into the row that is currently being acted upon, I have no idea how though. Also what if I add 5 rows at once ?
Thus, you might have learned how you can update the same row using an after insert trigger in SQL Server. In SQL Server, you cannot create an after insert trigger on a view. However, you can create an INSTEAD OF trigger to make the task done. Generally, a view is created from multiple base tables.
Now we are all set to try a few examples on UPDATE TRIGGER using this table. Example #1: Create a trigger in SQL, which automatically updates the date and time of shipping once the order status has been changed to shipped. The command has successfully created an UPDATE trigger, that gets invoked after successful update statement execution.
UPDATE Triggers are usually used in situations when we want to track details of modification on certain database values. For example, we want to capture the time of login and logout of a user from a website, restaurant, office etc. In the former case, we can use BEFORE UPDATE Trigger and AFTER UPDATE Trigger for the latter..
The trigger defined on the table will update the value of the grade column according to the condition defined on the marks. USE master GO CREATE TRIGGER TriggerGrade ON dbo.StudentMarks AFTER INSERT AS BEGIN DECLARE @Marks real, @Grade nchar (1), @StudentID int SELECT @Marks= INSERTED.Marks FROM INSERTED SELECT @StudentID= INSERTED.
You need to join the inserted
virtual table in the trigger to limit the rows that get updated to those actually changed. Try this:
CREATE TRIGGER ModDate
ON TableX
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE X
SET ModifiedDate = GETDATE()
FROM TableX X
JOIN inserted i ON X.key = i.key -- change to whatever key identifies
-- the tuples
END
Like @ZoharPeled correctly pointed out in a comment below there's really not much point in having the trigger update the date on insert - it would be better to use getdate()
as the default value on the column (or even as another column InsertedDate
if you want to track when records were initially created) and have the trigger only modify the ModifiedDate
column after updates.
See the documentation for more information on the inserted and deleted tables.
CREATE TRIGGER ModDate
ON TableX
FOR INSERT, UPDATE
AS
BEGIN
UPDATE TableX
SET ModifiedDate = GETDATE()
WHERE Id = (Select Id from Inserted)
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With