RDBMS PLSQL Program 46 program to generate before insert,update and delete row trigger on emp table which will not allow changes in data before and after office hours using RAISE_APPLICATION_ERROR() function

 CREATE OR REPLACE TRIGGER Working_hrs

BEFORE INSERT OR UPDATE OR DELETE ON emp

DECLARE

WORKING_HOURS NUMBER(2);

BEGIN

WORKING_HOURS := TO_CHAR(SYSDATE,’HH24′);

IF WORKING_HOURS < 9 AND WORKING_HOURS > 17 THEN

RAISE_APPLICATION_ERROR(-20001,'Its not official working hours! Can not do operations on emp');

END IF;

END;

/

RDBMS PLSQL Program 45 program to generate before insert row trigger on emp table which will not allow salary less than 1000 using RAISE_APPLICATION_ERROR() function

CREATE OR REPLACE TRIGGER minSalary 

BEFORE INSERT ON emp

FOR EACH ROW

BEGIN

IF (:new.sal < 1000) THEN

RAISE_APPLICATION_ERROR (-20004, ‘Minimum Salary should be 1000’);

END IF;

END;

/

 

RDBMS PLSQL Program 44 program to generate before insert, update and delete row trigger on emp table

 SET Serveroutput ON

CREATE OR REPLACE TRIGGER display_salary_changes  

BEFORE DELETE OR INSERT OR UPDATE ON EMP  

FOR EACH ROW  

DECLARE  

   sal_diff number(10,2);  

BEGIN  

   sal_diff := :NEW.esalary  - :OLD.esalary;  

   dbms_output.put_line('Old salary: ' || :OLD.esalary);  

   dbms_output.put_line('New salary: ' || :NEW.esalary);  

   dbms_output.put_line('Salary difference: ' || sal_diff);  

END;  

/  

RDBMS PLSQL Program 43 program to generate after update row trigger on emp table

 SET Serveroutput ON

CREATE OR REPLACE TRIGGER log_salary

AFTER UPDATE ON emp

FOR EACH ROW

BEGIN

  INSERT INTO Emp_log(Emp_id, Log_date, New_salary, Action)

  VALUES (:NEW.Eno, SYSDATE, :NEW.Esalary, 'New Salary');

END;6

/

RDBMS PLSQL Program 42 script to create emp_log table

 DROP TABLE Emp_log;

CREATE TABLE Emp_log (

  Emp_id     CHAR(6),

  Log_date   DATE,

  New_salary NUMBER(10,2),

  Action     VARCHAR2(20));

 

RDBMS PLSQL Program 41 program to generate before update row trigger on emp table

 SET Serveroutput ON

CREATE OR REPLACE TRIGGER display_salary  

BEFORE UPDATE ON EMP 

FOR EACH ROW  

BEGIN  

   dbms_output.put_line('Old salary: ' || :OLD.esalary);  

END;  

/  

RDBMS PLSQL Program 40 Script to create user in oracle other than sysadmin to generate triggers

connect system/password;

CREATE USER bca

  IDENTIFIED BY bca

  DEFAULT TABLESPACE USERS

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

-- 2 Roles for DEVELOPER 

GRANT CONNECT TO bca CONTAINER=ALL;

GRANT RESOURCE TO bca CONTAINER=ALL;

ALTER USER bca DEFAULT ROLE NONE;

-- 3 System Privileges for DEVELOPER 

GRANT CREATE SESSION TO bca;

GRANT CREATE TABLE TO bca;

GRANT CREATE TRIGGER TO bca;

GRANT CREATE SEQUENCE TO bca;

-- 2 Tablespace Quotas for DEVELOPER 

ALTER USER bca QUOTA UNLIMITED ON SYSAUX;

ALTER USER bca QUOTA UNLIMITED ON USERS;

RDBMS PLSQL Program 39 Program to call package to demonstrate procedure overloading

 SET Serveroutput ON

DECLARE

veno emp.eno%type;

vename emp.ename%type;

vesalary  emp.esalary%type;

vedoj emp.edoj%type;

vedno emp.edno%type;

veno1 emp.eno%type;

vename1 emp.ename%type;

vesalary1  emp.esalary%type;

BEGIN 

veno := '&veno';

vename := '&vename';

vesalary := &vesalary;

vedoj := '&vedoj';

vedno := '&vedno';

veno1 := '&veno1';

vename1 := '&vename1';

vesalary1 := &vesalary1;

emp_package.addemp(veno1,vename1,vesalary1);

emp_package.addemp(veno,vename,vesalary,vedoj,vedno);

END; 

/

RDBMS PLSQL Program 38 Program to create package body to demonstrate procedure overloading

 CREATE OR REPLACE PACKAGE BODY emp_package AS 

   PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary  emp.esalary%type)

    IS 

   BEGIN 

      INSERT INTO emp (eno,ename,esalary) 

         VALUES(veno, vename, vesalary); 

   END; 

   

   PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary  emp.esalary%type, vedoj emp.edoj%type, vedno emp.edno%type)

   IS 

   BEGIN 

      INSERT INTO emp (eno,ename,esalary,edoj,edno) 

         VALUES(veno, vename, vesalary, vedoj, vedno); 

   END; 

END emp_package; 

/

RDBMS PLSQL Program 37 Program to create package specification to demonstrate procedure overloading

 CREATE OR REPLACE PACKAGE emp_package AS 

   PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary  emp.esalary%type); 

   PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary  emp.esalary%type, vedoj emp.edoj%type, vedno emp.edno%type); 

END emp_package; 

/