August 11, 2008 at 7:32 am
Hi,
Earlier I created an instead of trigger to prevent updates containing a certain string to a number of columns in a table, I would also like to log the attempted updates to a log table.
So....I created the trigger using the code below;
CREATE TRIGGER trg_Trigger1 ON dbo.[Table1]
INSTEAD OF insert, update
AS
INSERT INTO tbl_TriggerLog(Insert1)
SELECT I.[Col1]
FROM inserted I
WHERE
I.[Col1] LIKE '%'+'SearchCriteria'+'%'
Now I have a slight problem, whilst I cannot update Col1 with the string and the update attempt is logged to the TriggerLog table I cannot update Col1 at all....with anything...lol.
Obviously the logic in the trigger is incorrect so I rewrote the trigger as below;
ALTER TRIGGER trg_Trigger1 ON dbo.[Table1]
INSTEAD OF insert, update
AS
BEGIN
IF EXISTS
(
SELECT I.Col1 FROM inserted
WHERE I.Col1 LIKE '%'+'SearchCriteria'+'%'
)
begin
INSERT INTO tbl_TriggerLog(Insert1)
end
ELSE
begin
update dbo.[Table1]
set [Col1]=inserted.Insert1
inner join inserted i on dbo.[Table1].Col1=i.Col1
end
end
I receive an error "Incorrect syntax near the keyword 'ELSE'."
If anyone could assist on the correct syntax and confirm if the new trigger logic is OK I would be very grateful.
Thanks
August 11, 2008 at 8:47 am
You arent inserting any values into tbl_TriggerLog, you either need a SELECT or a VALUES clause...
INSERT INTO tbl_TriggerLog(Insert1)
SELECT Value1
FROM Table
August 11, 2008 at 8:56 am
Don't use an Instead Of trigger for this. It will block legitimate inserts and updates, unless you code around them. Much easier to do in an After/For trigger.
CREATE TRIGGER trg_Trigger1 ON dbo.[Table1]
after insert, update
AS
if exists
(select *
from inserted
inner join Col1Rules
on inserted.Col1 like '%' + SearchCriteria + '%')
begin
rollback
INSERT INTO tbl_TriggerLog(Insert1)
SELECT [Col1]
FROM inserted
inner join Col1Rules
on inserted.Col1 like '%' + SearchCriteria + '%')
end
I'm assuming, in this, the existence of a table called "Col1Rules", with a column named "SearchCriteria", which would allow you to maintain a list of the criteria you want to check against. You might want something like that. Otherwise, use your Where clause. The table will almost certainly be easier to maintain, though.
The advantage to this is that it only rolls back transactions that violate the rules. An Instead Of trigger blocks all of the type of transactions it is set up for, and you have to write code into it to allow through the okay data.
Does that make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 11, 2008 at 9:18 am
Yes that makes perfect sense, and works very well. Thanks for the replies guys.
August 11, 2008 at 9:54 am
Just watch out for orphaned transactions when rolling back from a trigger!! It KILLS the entire "batch".
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply