Question

EX.NO. 6 SQL TRIGGER AIM To develop the package for employee management system using SQL triggers. DATABASE SCHEMA EMP (EMPID, NAME, SALARY, COMMISSION, DEPT NO) PROCEDURE STEP 1: Start STEP 2: Initialize the trigger with specific table id STEP 3: Develop and execute SQL trigger to carry out the following action. Create a trigger on EMP table which verify that no record has the Commission greater than salary of an employee in table Emp. STEP 4: Execute the trigger by performing the action specified. STEP 5: Stop

The procedure is a sample and needs to be modified.
The software used is oracle 11g.
Provide screen shot of the program running.

Answer 1

create table emp(EMPID int primary key,
NAME varchar(20),
SALARY int,
COMMISION INT,
DEPT_NO int);

set serveroutput on;
create or replace trigger mytrigger4
before insert on emp
FOR EACH ROW
DECLARE
ENDTRIGGER EXCEPTION;
begin
if inserting then
if :NEW.commision > :NEW.salary THEN
RAISE_APPLICATION_ERROR(-20000,'COMMISION CAN NOT BE GREATER THAN SALARY');
   end if;
   END IF;
EXCEPTION
   WHEN ENDTRIGGER THEN
   dbms_output.put_line('CANNOT INSERT');
END;
/

INSERT INTO EMP VALUES(1,'HARRY',100000,10,1);
INSERT INTO EMP VALUES(2,'JOE',100,1000,1);
INSERT INTO EMP VALUES(3,'JOHN',200,5000,1);
INSERT INTO EMP VALUES(4,'PETER',45000,1000,1);

SELECT * FROM EMP;

SQL> SQL> INSERT INTO EMP UALUES(1, 'HARRY",100000,10,1); 1 row created. SQL> INSERT INTO EMP UALUES(2, 'JOE',100,1000,1); INSERT INTO EMP UALUES(2, 'JOE,190,1909,1) ERROR at line 1: ORA-20000: COMMISION CAN NOT BE GREATER THAN SALARY ORA 06512: at "SCOTT.MYTRIGGER4", line 6 ORA-04 088: error during execution of trigger ·SCOTT.MY TRIGGER4 SQL> INSERT INTO EMP UALUES (3, JOHN',200,5000,1); INSERT INTO EMP UALUES(3,'JOHN" ,208,5999,1) ERROR at line 1: ORA-20000: COMMISION CAN NOT BE GREATER THAN SALARY ORA-06512: at "SCOTT.MYTRIGGER4", line 6 ORA-04 088: error during execution of trigger ·SCOTT.MYTRIGGER4 SQL> INSERT INTO EMP UALUES(4, 'PETER,45899,1808,1):; 1 row created. SQL> SQL> SELECT FROM EMP EMPID NAME SALARY COMMISION DEPT NO 1 HARRY 10 100000 45000 PETER 1090 SQL> I