Saturday, 1 October 2011

before insert or delete trigger

create table Employee(
     ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
     First_Name         VARCHAR2(10 BYTE),
    Last_Name          VARCHAR2(10 BYTE),
    Start_Date         DATE,
     End_Date           DATE,
     Salary             Number(8,2),
     City               VARCHAR2(10 BYTE),
    Description        VARCHAR2(15 BYTE)
  )
 
  insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  /
 
  /
 
 create or replace trigger emp_bid
     before insert or delete
     on employee
     referencing new as new old as old
    begin
       if to_char(sysdate,'Dy') in ('Sat','Sun') then
            raise_application_error(-20999,'No create/delete employees on weekend!');
        end if;
    end;
   /
  --After creating trigger when you are inserting into the tables means that
 
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  /
  Error report:
SQL Error: ORA-20999: No create/delete employees on weekend!
ORA-06512: at "APPS.EMP_BID", line 3
ORA-04088: error during execution of trigger 'APPS.EMP_BID'

No comments:

Post a Comment