How to create a delete trigger in SQL Server2012?

Question

How to create a delete trigger in SQL Server2012?

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I have a table called Productos in which I want to create a trigger which does not allow to delete a record when the existence of this product is greater than 0

I tried the following:

go

create trigger ProductoExistencia

on Productos for delete as

if((select Existencias from Productos) = 0)

delete from Productos inner join deleted on Productos.Id_Producto=deleted.Id_Producto 

else begin
raiserror('Cantidad mayor que cero',16,1)
rollback transaction

end
go

But it has not worked for me.

sql sql-server trigger

asked by Angel 01.12.2016 в 19:27

source

2 answers

0

You could create the Trigger and directly perform DELETE , if the record exists it is deleted:

CREATE TRIGGER ProductoExistencia
    ON Productos
    FOR DELETE
AS
DELETE Productos
    FROM DELETED D
    INNER JOIN Productos T ON T.Id_Producto = D.Id_Producto AND T.Existencias > 0
GO

UPDATE

In the statement of DELETE , AND T.Existencias > 0 is added to comply with the validation that is necessary

answered by 01.12.2016 в 19:45

Should I change the structure of my Akka project to integrate Play? does not send any kind of request when deleting record by ajax (jquery) and laravel

score 0 · Accepted Answer

Main option: Launch an error if you are trying to delete a product with Existencias > 0

With a IF EXISTS you can check if at least one of the records that is about to be deleted has the value of Existencias greater than zero. If this is the case, you throw an error to prevent delete . If not, there is no need to do anything, and delete will execute successfully.

create trigger ProductosDeleteTrigger
on Productos after delete as
begin
    if exists(select null
                from deleted
               where Existencias > 0)
    begin
        raiserror('Cantidad mayor que cero',16,1);
        rollback transaction;
        return;
    end;
end;
go

Alternative option: Ignore the product delete with Existencias > 0

Another option is to do a delete , instead of throwing an error when finding a record with Existencias > 0 , simply bypassing that record, not deleting it and continuing deleting the records that can be deleted .

Personally, I do not think it's a good idea to do it this way because that behavior is not intuitive and can cause confusion.

But since it seems that this is the option that @Flxtr is presenting in its response, I take the opportunity to include the correct way to do it.

The way to do it is using a INSTEAD OF trigger:

create trigger ProductosDeleteTrigger
on Productos instead of delete as
begin
  delete p
    from dbo.Productos p
    join deleted d
      on d.Id_Producto = p.Id_Producto
     and d.Existencias = 0;
end;
go

Unlike normal triggers that execute additionally to the statement that causes their execution, the INSTEAD OF triggers replace the statement.