Trigger Syntax Using Columns_updated
-
I am trying to create a trigger on a table with 20 columns. I want the trigger disallow updates to amount columns (columns 8 - 19) depending on another table. I am not sure about the "IF" statement.
CREATE TRIGGER ReadOnlyTrigger
on AmountTable
FOR insert, update, delete AS
IF (column updated falls in range defined in 2nd table)
RAISERROR ('Record is read-only.', 16, 1)
ROLLBACK
go
The 2nd table is defined as:
Version
StartMonth
StartYear
EndMonth
EndYear
Sample row: Actual, Jan, 2006, Aug, 2006
Thanks in advance.
-
Please look at instead of trigger. That might be what u want.
Thanks
sreejith
-
This does not solve the column mapping problem.
I have already coded something like this but I don't have it with me. I'll look into it tonight.
-
Something like this
IF ((SUBSTRING(COLUMNS_UPDATED(),1,1) & 128) > 0 OR
SUBSTRING(COLUMNS_UPDATED(),2,1) > 0 OR
(SUBSTRING(COLUMNS_UPDATED(),3,1) & 7) > 0) AND
EXISTS(SELECT * FROM [2nd table] WHERE ...)
RAISERROR ('Record is read-only.', 16, 1)
ROLLBACK
or test for each column and specific record
IF (SUBSTRING(COLUMNS_UPDATED(),1,1) & 128) > 0 AND -- col 8
EXISTS(SELECT * FROM [2nd table] WHERE ...)
RAISERROR ('Record is read-only.', 16, 1)
ROLLBACK
IF (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1) > 0 AND -- col 9
EXISTS(SELECT * FROM [2nd table] WHERE ...)
RAISERROR ('Record is read-only.', 16, 1)
ROLLBACK
IF (SUBSTRING(COLUMNS_UPDATED(),2,1) & 2) > 0 AND -- col 10
EXISTS(SELECT * FROM [2nd table] WHERE ...)
RAISERROR ('Record is read-only.', 16, 1)
ROLLBACK
etc
What is the relationship between each column and the 2nd table ?
Far away is close at hand in the images of elsewhere.
Anon. -
The version is on both tables and the months are columns in table1 where table 2 specifies a range. For example,
I try to update, insert or delete a value into table 1 for Actual, June 2006. The 2nd table used to limit the functions is specified with Actual, Jan, 2006, Aug, 2006. because the value is for June 2006, it should not be allowed to change or insert the value. If the value was for Sept 2006, the update should be allowed.
-
Sorry for the loss of formatting, but this is what you need. The only modifications you'll have to bring to this code are the tablenames and the list of columns to be allowed :
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
-
Ninja,
Aren't u doing "Instead of Trigger" like I mentioned?
Thanks
Sreejith
-
Neither of the triggers must be done here.
What needs to be done is a guy who use to draw spreadsheets must learn some basics about relational databases and stop putting different ranges of the same kind of data into different columns.
Such behaviour contradicts relational model, that's why any attempt to do so in RDMS always will cause major problems.
Place your data in tables proper way and you'll never face problems with managing it.
_____________
Code for TallyGenerator -
I didn't mean that my solution didn't involve a trigger. What I meant is that the he needed more details to provide a solution.
-
Amen Sergiy!
However, if that can't be done like in my case where I had no control over the 3rd party application. He'll have a 2nd option to block updates on a dynamic list of columns. I once wrote that trigger like that because the table was being modified like 3-4 times a month and I didn't want to have to re-update the trigger every time. What's great about this trigger is that the table definition can change all you want (assuming your green list don't get renamed) and the trigger will still allow to update only the allowed list of columns.
The only modifications that this trigger would need is if you needed to add more columns to the green list, then you'd have to update the update statement and the "in list". But that's like the easiest part of the job at that point
. -
Sergiy, your right this is not the "proper" way of solving this problem if you were creating the DB or had full control. Like Ninja mentioned with his problem, I am working with a 3rd party application which I don't have that luxury so I am trying to get creative to meet our needs while maintaining the approval of the vendor.
Thanks for the help!
-
Nice, to know I'm not the only one in that situation
. Anyways, have a look at the exists part of the trigger with in(col1, col2) part. Then tell me if you need help implementing it. -
Do you know anybody who is NOT in this situation?
_____________
Code for TallyGenerator -
Well I wasn't. For the first 2 minutes I worked there
.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply