August 11, 2006 at 3:04 pm
I have a table
col1 col2
one mandatory
two not mandatory
three not mandatory
four not mandatory
five mandatory
I am trying to write a trigger that will prevent uses from deleting or updating the mandatory records (rows 1 and 5). Something like
If you update or delete where col1 is one, five then rollback the transaction. I know this will hit me at 3 in the morning Sunday night but I would apprieciate some expertise here. Thanks
August 12, 2006 at 5:52 pm
CREATE TRIGGER TR_TEST_MANDATORY ON dbo.TEST
AS
SET NOCOUNT ON
IF EXISTS(SELECT col1 from inserted WHERE col2 = 'mandatory')
--IF EXISTS(SELECT col1 from inserted WHERE col1 in ('one','five'))
BEGIN
ROLLBACK TRANSACTION
END
August 14, 2006 at 7:56 am
Thanks Jo, very simple and effective.
I changed the trigger to this and it worked great.
CREATE TRIGGER TR_TEST_MANDATORY ON dbo.TEST for UPDATE, DELETE
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT col1 from inserted WHERE col1 in ('one','five'))
BEGIN
ROLLBACK TRANSACTION
END
August 14, 2006 at 2:30 pm
The condition is still
If you update or delete where col1 is one or five then rollback the transaction ?
The current code wants that one of the records contains col1=1 or 5, otherwise it will rollback.
August 14, 2006 at 2:47 pm
Right, it doesn't work.
The trigger you gave me allows me to change rows one and five but I can't changes them back. Example
UPDATE TEST
SET col1 = 'one_test'
WHERE col1 = 'one'
(changes the row but I can't change it back
UPDATE TEST
SET col1 = 'one'
WHERE col1 = 'one_test'
(fails)
On the other hand my script prevents
UPDATE TEST
SET col1 = 'one_test'
WHERE col1 = 'one'
but also prevents
UPDATE TEST
SET col1 = 'two_test'
WHERE col1 = 'two'
What do I do? Thanks
August 14, 2006 at 4:08 pm
pity me, I switched deleted (previous data) with inserted (new data)
CREATE TRIGGER TR_TEST_MANDATORY ON dbo.TEST for UPDATE, DELETE
AS
SET NOCOUNT ON
IF EXISTS(SELECT col1 from deleted /*old data*/ WHERE col1 in ('one','five'))
BEGIN
ROLLBACK TRANSACTION
END
Now it shouldn't be longer possible to change col1 from 'one' to 'one_test'
since one of the rows in the deleted table contains a record with col1='one'
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply