Prevent insert or update when there is already a record in the same group (triggers)

Question

Prevent insert or update when there is already a record in the same group (triggers)

Navigation

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

0

I want to make it impossible to insert or update when there is already a coordinador in the same departamento in a table empleados .

CREATE OR REPLACE  TRIGGER UnCoordinador
 AFTER UPDATe
 ON EMPLEADOS
  DECLARE
    BEGIN

        IF (SELECT COUNT(EMP_NO) FROM EMPLEADOS 
            WHERE TIPO='COORDINADOR')<1 
        BEGIN
            INSERT INTO EMPLEADOS
    END
    ELSE
    BEGIN
        raiserror('registro NO agregado', 10, 1) 
                ROLLBACK TRANSACTION
    END
END

sql plsql

asked by user7407723 13.06.2017 в 13:25

source

2 answers

How to control the Leave or Validated event of a textbox inside an MDI container? C # Why does not my Audio on Android compiled with Cordova play me?

score 0 · Answer 1

I think there are several points that affect your purpose: - The trigger you use is released after the table has been updated, it does not affect the insert - To be released first you must use instead of

CREATE TRIGGER UnCoordinador
  ON EMPLEADOS
  instead of insert
as  
BEGIN
    IF (SELECT COUNT(EMP_NO) FROM EMPLEADOS 
        WHERE TIPO='COORDINADOR')=0
    BEGIN
        INSERT INTO EMPLEADOS (columas....) (select columnas.. from inserted)
    END
    ELSE
    BEGIN
        raiserror('registro NO agregado', 10, 1) 
        ROLLBACK TRANSACTION
    END
END

Correction * I think you use Oracle DB, there exists before to use instead of instead of update

score 0 · Answer 2

You must change the moment in which the Trigger is going to execute. In the code you use the trigger AFTER (after) when it should be BEFORE (before).

And if you need it to be before the update and the insert you must also specify it.

CREATE OR REPLACE  TRIGGER UnCoordinador
 BEFORE UPDATE OR INSERT
 ON EMPLEADOS
  DECLARE
    BEGIN

        IF (SELECT COUNT(EMP_NO) FROM EMPLEADOS 
            WHERE TIPO='COORDINADOR')<1 
        BEGIN
            INSERT INTO EMPLEADOS
    END
    ELSE
    BEGIN
        raiserror('registro NO agregado', 10, 1) 
                ROLLBACK TRANSACTION
    END
END