November 30, 2005 at 11:45 pm
Hi Richard,
I Agree that INSERTED and DELETED tables have blob fields within INSTEAD OF triggers. Thank you for correcting the mistake. However, I disagree with what you say with UPDATETEXT/WRITETEXT. If you are using stored procedures to update the tables, you can always refer the additional column (with less cost) and update last_modified field. Why do you want triggers on the first place. Triggers is one of the mechanism where we can control ad-hoc queries. What about adhoc queries using UPDATETEXT/WRITETEXT? It shows that, triggers is not the best solutions.
On the other hand, triggers could be used against tables which do not have blob fields. I use it. it is one of the best methods to add audit component against, existing table.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
December 1, 2005 at 7:26 am
"What about adhoc queries using UPDATETEXT/WRITETEXT?"
I would never allow such a thing, unless it was in a custom SP that updated the audit table in later statement. The same is true if you update a field in the original table: I would never allow it outside a custom SP. Therefore, I would not worry about the trigger-firing issue. But it is good that you pointed out this undocumented (in BOL, AFAIK) problem.
I would guess that your need to write/update text blobs line by line is very unusual. I guess that >90% of databases will not encounter this issue. But if you need it, you need it!
Good luck,
Rich
December 1, 2005 at 7:49 am
"On the other hand, triggers could be used against tables which do not have blob fields. I use it. it is one of the best methods to add audit component against, existing table."
I just re-read your post, and I think I understand you point a bit better. I think it is reasonable to write audits into a text/ntext field, although I have not seen anyone else doing that. You could write into the same record or into a blob in another audit table (This may be easier and more flexible). However, in either case, you should be able to write to the blob field inside an "Instead Of" trigger.
In response to the previous post by Serqiy, this does NOT fire reentrant/recursive triggers by default. I don't know what will happen if you turn on the recursive_triggers option - but I would not want to try it.)
December 2, 2005 at 2:48 am
Hi Sergiy,
There's no need to. An INSTEAD OF UPDATE trigger will enver fire on an UPDATE statement that is executed in the context of the trigger. Cut and pastte the code below in Query Analyzer for a quick proof.
CREATE TABLE Test (A int NOT NULL PRIMARY KEY,
B int)
go
INSERT INTO Test (A, B)
VALUES (1, 1)
go
CREATE TRIGGER TestTrig
ON Test
INSTEAD OF UPDATE
AS
PRINT 'Trigger nest level ' + CAST(TRIGGER_NESTLEVEL() AS varchar(3))
UPDATE Test
SET B = (SELECT inserted.B
FROM inserted
WHERE inserted.A = Test.A)
WHERE EXISTS (SELECT inserted.B
FROM inserted
WHERE inserted.A = Test.A)
go
UPDATE Test
SET B = 2
WHERE A = 1
go
SELECT * FROM Test
go
DROP TRIGGER TestTrig
DROP TABLE Test
go
Best, Hugo
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply