Trigger which arrow default value for a field

Asked

Viewed 138 times

0

follows the statement:

If the employee department code is not Insert or if it does not exist in the table , assign to it the value standard 20;

And I made the following code:

create or replace trigger checar_cod_dept after insert or update of DPT_ID 
    on EMPREGADOS for each row when (DPT_ID not in (select ID from DEPARAMENTO))
begin 
     :new.id := 20;
end;

tables:

CREATE TABLE EMPREGADOS (
    NOME VARCHAR2(50) PRIMARY KEY,
    SALARIO number(2),
    DPT_ID NUMBER,

    CONSTRAINT DPT_ID_FK FOREIGN KEY (DPT_ID) REFERENCES DEPARTAMENTO(ID)  
);

CREATE TABLE DEPARTAMENTO (
    ID NUMBER PRIMARY KEY,
    NOME VARCHAR2(50)    
);

With the code I tried to use, the error because you can’t use Queries... I couldn’t think of another way.

Thank you

sql oracle trigger

  • Hi hi hi, at first glance I would say that you do not need triggers the "default on null 20" in create table or alter table, would solve the problem, additionally it may be necessary to use "CONSTRAINT DPT_ID_FK FOREIGN KEY (DPT_ID) REFERENCES DEPARTMENT(ID) ENABLE NOVALIDATE;" not to validate constraints if data exists and you make an alter table.

  • So I believe my teacher requested this exercise with Trigger for educational purposes only

  • Did he really ask with Rigger? In fact, the use of triggers in a database should be avoided (for example, by loss of performance), and as already noted there are other limitations. As for the exercise and what idea I think it would be to see the solution presented, what it seems to me is something as simple as a default, validates if it solves, however there is something you have to guarantee, the ID 20 will always have to exist in the DEPARTMENT table, this can be generated at the time of creating the database when running a default data SEED, then inject this value always.

  • By securing the ID 20 in the DEPARTMENT table you are ensuring the integrity of the ER database. You have here more detail on this.

  • create or replace Trigger checar_cod_dept after Insert or update of DPT_ID on EMPREGADOS for each Row Begin If :new.dpt_id is null then :new.dpt_id := 20; end if; end;

  • It should not be BEFORE Insert or update?

  • Yes, before of course !

Show 2 more comments

1 answer

0

I validated with Sqlfiddle, I do not have Oracle on my machine. When entering the record without the DPT_ID column sets the default 20. 

CREATE TABLE DEPARTAMENTO (
    ID NUMBER PRIMARY KEY,
    NOME VARCHAR2(50)    
);


CREATE TABLE EMPREGADOS (
    NOME VARCHAR2(50) PRIMARY KEY,
    SALARIO number(2),
    DPT_ID NUMBER DEFAULT 20,

    CONSTRAINT DPT_ID_FK FOREIGN KEY (DPT_ID) REFERENCES DEPARTAMENTO(ID)
);


INSERT INTO DEPARTAMENTO
(ID, NOME )
VALUES
(20, 'NOME departamento' );

INSERT INTO EMPREGADOS
(NOME, SALARIO )
VALUES
('NOME empregado', 10 );

Browser other questions tagged sql oracle trigger

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