Need help on PL/SQL database trigger

I have a DB trigger before insert on emp table. I would like to add sal, comm from emp_test table and want to use those value as a default in the emp table, by trigger. Any idea how to do it?

before insert on emp

sal, comm from emp_test

emp

1 Answer
1

Until you provide answers to questions I posted in a comment, here's how you might do it. See if you can adjust it.

EMP_TEST table contains only one row (which is kind of stupid; you'd rather use DEFAULT value for those columns in the EMP table).

SQL> create table emp_test (sal number, comm number);

Table created.

SQL> insert into emp_test (sal, comm) values (3000, 100);

1 row created.


Trigger takes SAL and COMM values if they are provided; otherwise, it takes values from the EMP_TEST table.

SQL> create or replace trigger trg_bi_emp
2 before insert on emp
3 for each row
4 begin
5 select nvl(:new.sal, t.sal),
6 nvl(:new.comm, t.comm)
7 into :new.sal,
8 :new.comm
9 from emp_test t;
10 end;
11 /

Trigger created.


Testing: I didn't provide SAL value (so trigger will insert EMP_TEST one), but I did provide COMM:

SQL> insert into emp (empno, ename, deptno, sal, comm)
2 values (1, 'Littlefoot', 40, null, 50);

1 row created.

SQL> select * from emp where empno = 1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
1 Littlefoot 3000 50 40

SQL>


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.