December 13, 2009 at 7:17 pm
Hi all,
I have table Demo:
CREATE TABLE Demo
(
ID int PRIMARY KEY,
Name char(50),
BeModified int DEFAULT(0)
)
Whenever [Name] is updated, [BeModified] will be set to 1.
I think I should create an TRIGGER AFTER UPDATE
CREATE TRIGGER UpdateRow
ON Demo
AFTER UPDATE
UPDATE Demo
Set BeModified = 1 WHERE ID = "???"
My problem is I do not know how to identify the ID of row which has been modified.
Have you got any suggestion for me?
Thank you so much!
December 13, 2009 at 7:50 pm
In a trigger you would use the "inserted" virtual table.
update demo
set bemodified = 1
from inserted i
where demo.id = i.id
Be aware that triggers fire once for ALL rows updated in a statement, so this would update multiple rows with bemodified if multiple rows were changed. also be aware that you're setting to a scalar value. Typically you would use a date, so you can determine what was updated last.
December 13, 2009 at 8:50 pm
It is exactly what I need. Thanks for your helping!
December 14, 2009 at 6:46 am
Why do it with a trigger? Why not do it in the T-SQL that updates NAME? So your code doing the NAME update would be:
Update demo
Set name = 'new name',
bemodified = bemodified + 1
I'm assuming that you want it to increment whenever name is modified.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 14, 2009 at 6:59 am
Because I am doing something like an audit action.
December 14, 2009 at 7:40 am
I understand that you are trying to audit, but updating the bemodified column when you do the update on the name is in essence doing the exact same thing as the trigger, but without the overhead of the trigger and 2 updates against the same table. I understand that a trigger can protect you against ad hoc updates from outside the application, but I had to ask the question.
Also realize that doing:
Update demo
set name = name
Will fire the trigger and cause the bemodified column to be changed as well. So you may want to verify that the name is actually changed when you do the update by modifying Steve's code to something like this:
update demo
set bemodified = 1
from inserted i
where demo.id = i.id and
demo.name <> i.name
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 14, 2009 at 8:47 am
Great point, Jack. You can also use the UPDATE() function in the trigger to check things. http://msdn.microsoft.com/en-us/library/ms187326%28SQL.90%29.aspx
December 15, 2009 at 7:21 am
@jack-2: My English is not so well, so I am sorry if I may not be able to understand all you talked.
I have understood it as following:
TABLE DEMO(ID, Name, BeModified)
BeModified = 1: It has been modified.
BeModified =0 : It hasn't been modified.
Using AFTER TRIGGER helps you so set BeModified = 1 when the correspondent row was updated. You, then, view this result of BeModified for the first time. Next, you want to set BeModified = 0, because you expect that after that if there is something updating your database, BeModified will be once again set to be 1. The problem is you can't set BeModified = 0 because this is also an update action.
If this is exactly what you asked. I have my solution. I think it should distinguish between wether it is my own update action or not. If it is mine, BeModified =0, vice versa, BeModified =1. So I use a new field [SetNull] as a control. SetNull = 1: it is my update action, setnull = 0: update action is not mine.
This will meet trouble if someone doestn't modify other fields but [setnull]. I am just a beginner in SQL, so this is just for reference & for fun, not the best way 😀
-----------------------------
ALTER TABLE Demo
DROP Column BeModified
-------------------------------
ALTER TABLE Demo
ADD BeModified int not null default(0)
-------------------------------------
ALTER TABLE Demo
ADD SetNull int not null default(1), LastModefiedDay datetime default(getdate())
-------------------------------------
CREATE TRIGGER DemoUpdate
ON dbo.Demo
AFTER update
AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE Demo
SET Demot.BeModified = 1, LastModifiedDay=getdate(), Demo.SetNull=0
from deleted j
WHERE Demot.ID = (select j.ID where j.SetNull=1)
UPDATE Demo
SET Demo.BeModified = 0
from inserted i, deleted j
WHERE Demo.ID = (select j.ID where j.SetNull=0)
AND
Demo.ID = (select i.ID where i.SetNull=1)
End
December 15, 2009 at 7:28 am
If all you are concerned about is if the name column has been modified I'd recommend just using a last modified date. That will tell you when the column was last changed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 15, 2009 at 8:06 am
😀 .... what I concern is how many rows were updated.
Anyway, thanks for your interest!
December 16, 2009 at 12:52 pm
maiphuong2703 (12/15/2009)
@Jack: My English is not so well, so I am sorry if I may not be able to understand all you talked.I have understood it as following:
TABLE DEMO(ID, Name, BeModified)
BeModified = 1: It has been modified.
BeModified =0 : It hasn't been modified.
Using AFTER TRIGGER helps you so set BeModified = 1 when the correspondent row was updated. You, then, view this result of BeModified for the first time. Next, you want to set BeModified = 0, because you expect that after that if there is something updating your database, BeModified will be once again set to be 1. The problem is you can't set BeModified = 0 because this is also an update action.
If this is exactly what you asked. I have my solution. I think it should distinguish between wether it is my own update action or not. If it is mine, BeModified =0, vice versa, BeModified =1. So I use a new field [SetNull] as a control. SetNull = 1: it is my update action, setnull = 0: update action is not mine.
This will meet trouble if someone doestn't modify other fields but [setnull]. I am just a beginner in SQL, so this is just for reference & for fun, not the best way 😀
-----------------------------
ALTER TABLE Demo
DROP Column BeModified
-------------------------------
ALTER TABLE Demo
ADD BeModified int not null default(0)
-------------------------------------
ALTER TABLE Demo
ADD SetNull int not null default(1), LastModefiedDay datetime default(getdate())
-------------------------------------
CREATE TRIGGER DemoUpdate
ON dbo.Demo
AFTER update
AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE Demo
SET Demot.BeModified = 1, LastModifiedDay=getdate(), Demo.SetNull=0
from deleted j
WHERE Demot.ID = (select j.ID where j.SetNull=1)
UPDATE Demo
SET Demo.BeModified = 0
from inserted i, deleted j
WHERE Demo.ID = (select j.ID where j.SetNull=0)
AND
Demo.ID = (select i.ID where i.SetNull=1)
End
I think what you really want in your trigger is something like this:
UPDATE Demo
SET Demo.BeModified = 0
WHERE Demo.BeModified = 1
AND Demo.ID NOT IN
(
SELECT ID
FROM deleted
)
UPDATE Demo
SET Demo.BeModified =
CASE
Demo.Name <> j.Name THEN 1
ELSE 0
END
, LastModifiedDay=getdate()
FROM deleted j
WHERE Demo.ID = j.ID
AND Demo.Name <> j.Name
Your setnull should not be necessary because, even if you allow recursion in triggers, the values set by the trigger would not be modified by the second firing. Also, I do not believe that your WHERE clauses are going to select the rows you think they will -- I don't beleve that's even legal SQL.
Of course, all of this presumes that your updates are done at some regular interval or due to some recognizable event and you want the "BeModified" flag to indicate whether it was modified by the last pass. Without something like that, since BeModified gets set or reset each time someone updates the table, it will have little meaning to any arbitrary reader because for a given record it may have been set and reset many times before they read it and its value at that time is just a random snapshot.
-- Les
December 16, 2009 at 12:55 pm
Even with this, I'd argue you won't know.
If I update row 12 now and you update row 16 in a minute, we'll only see the last update (row 16). If I'm not aware of that update, or I've said it's the last update, people will get confused quickly.
If you're doing this, why not use a date? It doesn't really take much more space and it will be more helpful.
December 16, 2009 at 1:59 pm
That's why I was presuming the edits occurred at a regular interval or some other recognzable event. If updates are only done by a single process every day at Midnight, say, then anyone reading the table later in the day can see what records were changed last night. But if updates are being done at unknown times by unknown processes, BeModified has no particularly useful information.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply