CODEWITHSUNDEEP
sql-servert-sqldatabase-trigger
uberxeq
uberxeq

Reputation: 43

Trigger on update to prevent update when isedit != 1

I have a table:

ID NAME isedit
1  jon   0
2  smit  1
3  eve   0

I need to create a trigger that prevents the row from being updated when isedit is NOT 1.

It must however allow me to set isedit in the first place e.g.

update base SET isedit = 0 WHERE id = @id

I tried the following:

CREATE TRIGGER dbo.onupdate
ON base
AFTER UPDATE
AS
  BEGIN
  If (SELECT isedit FROM base) NOT LIKE '1'
  Begin
    Return
  END    
END

But it doesn't make much sense I think.

Upvotes: 0

Views: 157

Answers (2)

Kedar Ghadge
Kedar Ghadge

Reputation: 144

Please try this logic. When you trying to update something basically insert and delete operation will get perform in SQL.

So, when you perform update it deletes your original row and keeps it under the logical table of the name 'deleted' and inserts a new row from the logical table of the name 'inserted'.

in this way you can fetch the value from the table 'deleted' and identify the value of the column isEdit != 1 then it will throw an error with rollback transaction.

Create TRIGGER [dbo].onupdate
ON [dbo].base
FOR UPDATE
AS
    IF EXISTS(SELECT NULL FROM deleted where isEdit <> 1)
    BEGIN
        RAISERROR('You can not update when isEdit value is 1', 16, 1)
        ROLLBACK TRAN
    END

Upvotes: 0

Dale K
Dale K

Reputation: 27462

  1. Use set based logic, taking into account that Inserted can have multiple (or zero) records.
  2. Use rollback to undo the update when its not valid to do so.
  3. To allow you to set and clear isedit, because you update it as a single column update you can use the update(column) function to test for a change to that column and allow it to pass.
    create trigger dbo.onupdate
    on base
    after update
    as
    begin
      set nocount on;

      if not update(isedit) and exists (
        select 1
        from Inserted I
        where isedit != 1
      ) begin
        rollback;
      end;
    end

Upvotes: 1

Related Questions