Update records using a TRIGGER FOR DELETE using SQL Server

Question

Update records using a TRIGGER FOR DELETE using SQL Server

Navigation

#1 by (3 votes)

0

I want to make a trigger that as soon as I make a dismissal to the rest of the employees I raise the salary by 20%, but I do not know how to operate directly with the cursor explícito in this trigger , since the query would give me back several values.

CREATE TRIGGER despemp
    ON Empleado
    FOR delete
    AS
        BEGIN
            IF  exists (select e# from deleted where (e# = (select e# from Empleado)))
                BEGIN
                    DECLARE @salariomas float = 0.2*(select salario from Empleado) + (select salario from Empleado);
                    BEGIN TRY
                        update Empleado set salario = @salariomas;
                    END TRY
                    BEGIN CATCH
                        print 'No se ha podido realizar la inserción'
                    END CATCH
                END
    END

sql-server tsql cursor trigger

asked by ras212 14.02.2017 в 23:03

source

1 answer

List data with SQL Prepared Statements Make an image to be circular in Swift

score 3 · Accepted Answer

For what you describe in your question and comments, it seems that doing delete to a record in the Empleado table is equivalent to dismissing it. And your desire is to increase the salary by 20% to all other employees when you dismiss (eliminate) an employee.

Although this sounds a bit weird, it is possible. But there is no need to use cursors. It can simply be done with a simple UPDATE within the trigger:

CREATE TRIGGER despemp ON Empleado FOR delete AS
  set nocount on;
  update Empleado set salario *= 1.2;
go

The only detail is that if your DELETE sentence eliminates more than one employee at a time (or does not eliminate any), there will only be a single 20% increase for all employees.

If necessary, the trigger can be adjusted to be more precise. That is, if the DELETE does not eliminate any employee, nobody receives an increase. And if the DELETE eliminates several employees at the same time, the 20% increase is applied the same number of times as the number of employees eliminated.

To do this, you just have to adjust the formula to take into account the number of employees eliminated:

CREATE TRIGGER despemp ON Empleado FOR delete AS
  set nocount on;

  declare @delCount int;
  select @delCount = count(*) from deleted;

  if @delCount > 0
    update Empleado set salario *= power(1.20, @delCount);
go