Trigger Based on Column Name not Column #
-
I've written a trigger that was executed when a specific column was updated but that column was based on it's bitmask value.
Is there a way to execute a trigger based on a column name and not it's bitmask value?
The reason I ask is....
If another column is added to that table before the trigger column won't it change the value of any column after the new one thus "breaking" the trigger?
-
My first comment is why the heck would you want to do that (add a column in a specific space in the table)?
But here's the answer to your problem anyhow, this is a full production trigger, I'm posting the whole thing so you can figure out what happens :
CREATE TRIGGER [trAVT_FACTMA_BlockUpdatesDeletes] ON [dbo].[AVT_FACTMA]
INSTEAD OF UPDATE, DELETE
AS
SET NOCOUNT ON
--this trigger forbids any deletes to be made, also it will allow updates to be made only on the FFPRINTED column, anything else will trigger an error
Declare @ErrMsg as varchar(1000)
Declare @TableName as sysname
, @TriggerName as sysname
IF
EXISTS (SELECT * FROM Deleted) AND EXISTS (SELECT * FROM Inserted) --checking it's not a delete query
AND NOT EXISTS (
SELECT *
FROM dbo.SysColumns
WHERE id = (SELECT parent_obj from dbo.SysObjects WHERE id = @@procid) --this allows for the tablename to be changed without affecting the trigger (vs hardcoding the tablename in the trigger)
AND Name not in ('FFPRINTED', 'Solde') --allowed column to be updated
AND SUBSTRING(COLUMNS_UPDATED(), CAST(CEILING (Colid / 8.0) AS INT), 1)
& POWER(2, CASE WHEN Colid % 8 = 0 THEN 8 ELSE Colid % 8 END - 1) > 0
 
BEGIN
--UPDATE ALLOWED LIST of columns
UPDATE FA
SET FA.FFPRINTED = I.FFPRINTED
, FA.Solde = I.Solde
FROM dbo.AVT_FACTMA FA INNER JOIN Inserted I ON FA.FFNOFACT_INT = I.FFNOFACT_INT
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM Deleted)
BEGIN
Select @TableName = OBJECT_NAME (parent_obj), @TriggerName = OBJECT_NAME(@@procid) FROM dbo.SysObjects WHERE id = @@procid
SET @ErrMsg = 'The table ' + @TableName + ' doesn''t allow deletes nor updates (' + @TriggerName + ')'
RAISERROR (@ErrMsg, 13, 1)
END
--ELSE
--BEGIN
--@@ROWCOUNT = 0, no need to generate any err msg.
--END
END
GO
Please note that this trigger forbids any update except on a predefined list of columns. But you'll get the idea once you play with the code.
-
Thanks for the reply Ninja's_RGR'us!!!!!
To answer your question "Why would I like to add a column in a specific space in a table?" is: I like to group columns of similiarity together or because it's nice to maintain a standard for similiar tables...take for instance a directory table that has CustomerName,Address1,Address2,City,State,Zip,Active etc.. it's also nice to know where a column is when you have a tabe that has lots of columns. For instance I have an active column in almost every table and we like to keep that column at the end of the table. I always know where to look when searching for Active status (Scroll,Scroll,Scroll).
The downside? You can break triggers!!!
Not sure what else you can break...any other ideas?
Thanks again for the post... I'll check out the code.
-
I'm here assuming that you are using Enterprise manager so here's a short list of what can break :
1 - Production DB
2 - System stop for EM to alter a bigger table
3 - SysDepends
4 - Client application
5 - Your neck (your job / reputation)
6 - Some indexed views (never got 100% confirmation on this one but I'm pretty sure).
-
Nice List!
I found an easier way to check if a column is being updated than using the Bitmask COLUMN_UPDATED() syntax:
UPDATE (Column Name)
-
Lol, it's always the easy ones that you seem to forget first
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply