Creating Trigger to change status once you delete employee

Asked

Viewed 1,813 times

1

I’m creating a Rigger that the moment you delete an employee she changes the status from 1 (active) to 0 (inactive) then save this change in a table (employees dismissed) to access if you need, however, I’m in trouble. As soon as I try to create Trigger SQL Server informs the following message

Message 213, Level 16, State 1, Trigfunctionemitted Procedure, Line 23 Column name or number of values provided does not match the table definition. Citation

CREATE TRIGGER TrigFuncionarioDemitido
ON funcionarios
INSTEAD OF  DELETE
AS
BEGIN
       Begin Transaction F1
       -- DECLARAR UMA VARIมVEL
     DECLARE @id  INT
     -- ATRIBUIR VALOR A VARอAVEL @id
       SELECT @id =
                            (
                  SELECT D.id
                  FROM    deleted             AS D
                )
    -- 1) fazer o UPDATE na tabela de Funcionแrio
    -- atualizar o Status
    UPDATE funcionarios
    SET Status = 0
    WHERE  id = @id

    -- 2) FAZER UMA INSERวรO DO FUNCIONARIO DEMITIDO
    -- PARA A TBFUNICONARIO DEMITIDO
    INSERT INTO funcionariosdemitidos
    SELECT f.* , GETDATE ()
    FROM funcionarios AS f
    WHERE id = @id
    IF (@@ERROR = 0)
      begin
         commit transaction f1
       print 'Registro atualizado com sucesso'
     end
    ELSE
       begin
         rollback transaction f1
       print 'Registro nใo atualizado'
       end

END

Complete bank code and Trigger in the following link

https://www.dropbox.com/s/oruulm9n9crhdza/Codigo.txt

  • Andrey, thanks for the help in editing the post.

  • 1

    Employee check dismissed, if there is the table and if the columns are the same as employees plus a date column, the error seems to be there.

  • Thanks Motta! Thank you very much!

1 answer

1


After Motta’s observation I added the date field in the employee table dismissed and it worked.

Check employees dismissed, if there is the table and if the columns are the same as employees plus a date column, the error seems to be there. - Motta

In the code I was adding the getdate(), however, the employee table did not have these lines. Done this the Trigger ran straight.

Thank you!

  • Diogo, don’t forget to mark your own answer as the correct one!

  • Done, Andrey! Thanks too. Hug

  • Ah, Motta gets no score?

  • If you want Motta to score (it’s fair), write a comment in your question quoting his name (with @Motta) asking him to transcribe your comment as a question. So you can mark it as the right one and he’ll win the points.

  • Not for me, Diogo himself solved the bagasse, I just gave a happy guess !!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.