USING TRIGGER

Database triggers or triggers are SQL statements that are stored in the database catalog. Once triggers are activated by database events such as UPDATE, DELETE or INSERT, the triggers will execute either before or after the event that initiates them.In ORACLE how you can create triggers and use them...

create table employees
( id number primary key,
name varchar2(50));

create or replace trigger uppername
before insert or update on employees
for each row
begin
:new.name:=upper(:new.name);
end;
/

insert into employees values(1, 'Jubayer');

select * from employees;

create or replace trigger uppername
before insert or update on employees
for each row
begin
if inserting then
:new.name:=upper(:new.name);
elsif updating then
:new.name:=lower(:new.name);
end if;
end;
/

insert into employees values(2, 'khalid');

<<<<<<<<<<<<<<<<update>>>>>>>>>>>>>>>>>>>

create or replace trigger uppername
before insert or update on employees
for each row
begin
if inserting then
:new.name:=upper(:new.name);
elsif updating then
:new.name:=lower(:old.name);
end if;
end;
/

insert into employees values(3, 'Shoaib');

update employees set name='Amin' where id=3;



create table log(
log_data varchar2(50),
username varchar2(50),
timeofaccess date);

create or replace trigger uppername
before insert or update on employees
for each row
begin
if inserting then
:new.name:=upper(:new.name);
elsif updating then
insert into log values('updating', sys_context('userenv', 'session_user'), sysdate);
end if;
end;
/



create or replace trigger uppername
before insert or update on employees
for each row
begin
if inserting then
:new.name:=upper(:new.name);
elsif updating then
update employees set name='Sukarna' where id=1;
end if;
end;
/


create or replace trigger uppername
before insert or update on employees
begin
if updating then
update employees set name='Sukarna' where id=1;
end if;
end;
/


create or replace trigger uppername
before insert or update on employees
declare
temp_id number;
begin
if updating then
select max(id) into temp_id from employees;
insert into employees values(temp_id, 'Sayeed');
end if;
end;
/




create or replace trigger uppername
before insert or update on employees
declare
temp_id number;
begin
if updating then
select max(id) into temp_id from employees;
insert into employees values(temp_id+1, 'Sayeed');
end if;
end;
/


****************Can't
create or replace trigger id_gen
after insert on employees
for each row
declare
temp_id number;
begin
select max(id) into temp_id from employees;
:old.id:=temp_id+1;
end;
/


create or replace trigger id_gen
after insert on employees
for each row
declare
temp_id number;
begin
select max(id) into temp_id from employees;
:new.id:=temp_id+1;
end;
/

create or replace trigger id_gen
before insert on employees
for each row
declare
temp_id number;
begin
select max(id) into temp_id from employees;
:old.id:=temp_id+1;
end;
/


create or replace trigger id_gen
before insert on employees
for each row
declare
temp_id number;
begin
select max(id) into temp_id from employees;
:new.id:=temp_id+1;
end;
/

insert into employees values(12, 'Abu');


create or replace trigger id_gen
before insert on employees
for each row
declare
temp_id number;
begin
if :old.id is null then
select max(id) into temp_id from employees;
:new.id:=temp_id+1;
end if;
end;
/

create or replace trigger id_gen
before insert on employees
for each row
declare
temp_id number;
begin
if :new.id is null then
select max(id) into temp_id from employees;
:new.id:=temp_id+1;
end if;
end;
/



create or replace trigger after_create
after create on database
begin
insert into log values(ora_sysevent, sys_context('userenv', 'session_user'), sysdate);
end;
/


create or replace trigger logon_trigg
after logon on database
begin
insert into log values(ora_sysevent, sys_context('userenv', 'session_user'), sysdate);
end;
/

USING PL\SQL

DROP TABLE areas;
CREATE TABLE areas(
radius NUMBER(7,2),
area NUMBER(14,2)
);


DROP TABLE T_EMPLOYEE;
CREATE TABLE T_EMPLOYEE(
employee_id number,
employee_name varchar(50)
);

INSERT INTO T_EMPLOYEE VALUES('1','ABUL');
INSERT INTO T_EMPLOYEE VALUES('2','ABDUL');
INSERT INTO T_EMPLOYEE VALUES('3','ABir');
INSERT INTO T_EMPLOYEE VALUES('4','Alief');
INSERT INTO T_EMPLOYEE VALUES('5','ARIF');
INSERT INTO T_EMPLOYEE VALUES('6','ALIM');

CREATE TABLE RADIUS_VALS(
radius number(7,3)
);

INSERT INTO RADIUS_VALS VALUES('3');
INSERT INTO RADIUS_VALS VALUES('13');
INSERT INTO RADIUS_VALS VALUES('23');
INSERT INTO RADIUS_VALS VALUES('33');
INSERT INTO RADIUS_VALS VALUES('43');
INSERT INTO RADIUS_VALS VALUES('53');
INSERT INTO RADIUS_VALS VALUES('63');



<<<<<<<<<<<<<<<[Simple PL/SQL]>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.141534;
    radius INTEGER(5);
    area INTEGER(14,2);
BEGIN
    radius := 3;
    area := pi*power(radius,2);
    INSERT INTO AREAS VALUES(radius,area);
   
end;
/


<<<<<<<<<<<<<<<[EXCEPTION Handling]>>>>>>>>>>>>>>>>>>>>>>>


///////OUT PUT:ZERO VALUE INSERTED TO AREAS...AREA IS:...//////////////

SET SERVEROUTPUT ON;
DECLARE
      pi     constant NUMBER(9,7) := 3.1415927;
      radius INTEGER(5);
      area   NUMBER(14,2);
      some_variable   NUMBER(14,2);
BEGIN
      radius := 3;
      loop
            some_variable := 1/(radius-4);
            area := pi*power(radius,2);
                     insert into AREAS values (radius, area);
            radius := radius+1;
            exit when area >100;
      end loop;
 EXCEPTION
      when ZERO_DIVIDE
     then insert into AREAS values (0,0);
     DBMS_OUTPUT.PUT_LINE('Zero value inserted to AREAS. Area Is: '||area);
 end;
/



//////////////////////OUT PUT:HIGH AREA//////////////////////

SET SERVEROUTPUT ON;
DECLARE
      pi     constant NUMBER(9,7) := 3.1415927;
      radius INTEGER(5);
      area   NUMBER(14,2);
      some_variable   NUMBER(14,2);
    too_high_area  exception;
BEGIN
      radius := 3;
      loop
            some_variable := 1/(radius-4);
            area := pi*power(radius,2);
                     insert into AREAS values (radius, area);
            radius := radius+1;
       
    if area * 30 >100 then raise too_high_area;
    end if;

            exit when area >100;
      end loop;
 EXCEPTION
      when ZERO_DIVIDE
     then insert into AREAS values (0,0);
     DBMS_OUTPUT.PUT_LINE('Zero value inserted to AREAS. Area Is: '||area);
         when too_high_area then DBMS_OUTPUT.PUT_LINE('High AREA');

 end;
/







////////////////OUT PUT:NO DATA FOUND///////////////

DECLARE
    v_radius  NUMBER(10,3);
    v_area  NUMBER(7,3);
    too_high_area  exception;
BEGIN
    select radius, area into v_radius, v_area
        from areas where radius = '198';
    if v_radius * 30 > 20 then raise too_high_area;
    end if;
exception
    when NO_DATA_FOUND
    then DBMS_OUTPUT.PUT_LINE('No data found');
    when too_high_area then DBMS_OUTPUT.PUT_LINE('High AREA');
end;   
/





<<<<<<<<<[%type]>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.141532;
    radius areas.radius%type;
    area  areas.area%type;
BEGIN
    radius := 3;
    area := pi*power(radius,2);
    INSERT INTO AREAS VALUES(radius, area);
end;
/


<<<<<<<<<<<<<<<<<<<<[%rowtype]>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.141523;
    value areas%rowtype;
BEGIN
    value.radius := 3;
    value.area := pi*power(value.radius,2);
    INSERT INTO AREAS VALUES(value.radius, value.area);
end;
/

<<<<<<<<<<<<<<<<<<<<[%rowtype](Select)>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.141523;
    value areas%rowtype;
BEGIN
    SELECT MAX(RADIUS) INTO value.radius FROM areas;
    value.area := pi*power(value.radius,2);
    INSERT INTO AREAS VALUES(value.radius+1, value.area);
end;
/


<<<<<<<<<<<<<<<<<<[SELECT](NORMAL)>>>>>>>>>>>>>>>>>>>>>

SET SERVEROUTPUT ON;
DECLARE
    v_radius NUMBER;
    v_area NUMBER;
BEGIN
    SELECT radius, area into v_radius, v_area  FROM areas where radius=4;
    DBMS_OUTPUT.PUT_LINE('Radius Is:'|| v_radius||'AREA IS:'||v_area);
END;
/


<<<<<<<<<<<<<<<<[CURSOR]>>>>>>>>>>>>>>>>

SET SERVEROUTPUT ON;
DECLARE
    CURSOR empCursor IS select employee_id, employee_name from t_employee WHERE employee_name like 'A%';
    empvalue empCursor%rowtype;
BEGIN
    OPEN empCursor;
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS:'||empvalue.employee_name);
    DBMS_OUTPUT.PUT_LINE('BY: itsjubayer.blogspot.com');
    CLOSE empCursor;
END;
/


<<<<<<<<<<<<<<<<<[SELECT ROWS USING CURSOR]>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    CURSOR empCursor IS select employee_id, employee_name from t_employee;
    empvalue empCursor%rowtype;
    empvalue1 empCursor%rowtype;
BEGIN
    OPEN empCursor;
   
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
        FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    CLOSE empCursor;
END;
/


<<<<<<<<<<<<[USING %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT]>>>>>>>
// %NOTFOUND

DECLARE
    pi constant NUMBER(9,7) := 3.1415927;
    area NUMBER(14,2);
    CURSOR rad_cursor IS
        SELECT * FROM RADIUS_VALS;
    rad_val rad_cursor%ROWTYPE;
BEGIN
    OPEN rad_cursor;
    LOOP
    /* Within the loop, fetch a racord. */
    fetch rad_cursor into rad_val;
    /* IF the fetch attempt revels no more. */
    /* records in the cursor, then exit the loop. */
    EXIT WHEN rad_cursor%NOTFOUND;
    /* If the fetch attempt returned a record. */
    /* then process the radius value and insert */
    /* a record into the AREAS table . */
    area := pi*power(rad_val.radius, 2);
    INSERT INTO AREAS VALUES (rad_val.radius, 2);
    DBMS_OUTPUT.PUT_LINE(concat('Radius is: ', rad_val.radius));
    DBMS_OUTPUT.PUT_LINE(concat('Areas is: ',area));
END LOOP;
close rad_cursor;
end;
/



// %NOTFOUND

DECLARE
    pi constant NUMBER(9,7) := 3.1415927;
    area NUMBER(14,2);
    CURSOR rad_cursor IS
        SELECT * FROM RADIUS_VALS;
    rad_val rad_cursor%ROWTYPE;
BEGIN
    OPEN rad_cursor;
    LOOP
    fetch rad_cursor into rad_val;
    EXIT WHEN rad_cursor %NOTFOUND=true;
    area := pi*power(rad_val.radius, 2);
    INSERT INTO AREAS VALUES (rad_val.radius, 2);
    DBMS_OUTPUT.PUT_LINE(concat('Radius is: ', rad_val.radius));
    DBMS_OUTPUT.PUT_LINE(concat('Areas is: ',area));
END LOOP;
close rad_cursor;
end;
/








// %FOUND

DECLARE
    pi constant NUMBER(9,7) := 3.1415927;
    area NUMBER(14,2);
    CURSOR rad_cursor IS
        SELECT * FROM RADIUS_VALS;
    rad_val rad_cursor%ROWTYPE;
BEGIN
    OPEN rad_cursor;
    LOOP
    fetch rad_cursor into rad_val;
    EXIT WHEN rad_cursor %FOUND=false;
    area := pi*power(rad_val.radius, 2);
    INSERT INTO AREAS VALUES (rad_val.radius, 2);
    DBMS_OUTPUT.PUT_LINE(concat('Radius is: ', rad_val.radius));
    DBMS_OUTPUT.PUT_LINE(concat('Areas is: ',area));
END LOOP;
close rad_cursor;
end;
/




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<[LOOP]>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
      pi     constant NUMBER(9,7) := 3.1415927;
      radius INTEGER(5);
      area   NUMBER(14,2);
BEGIN
    /*  Specify the criteria for the number of loop executions. */
      for radius in 1..7 loop
    /*  Calculate the area using the current Radius value.      */
          area := pi*power(radius,2);
    /*  Insert the area and radius values into the AREAS table.   */
      INSERT INTO AREAS values (radius, area);
    /*  Signal the end of the loop.                      */
 end loop;
    DBMS_OUTPUT.PUT_LINE('Successful');
end;
/


<<<<<<<<<<<<<<<<<<<<<<<<[WHILE LOOP]>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi     constant NUMBER(9,7) := 3.1415927;
    radius INTEGER(5);
    area   NUMBER(14,2);
BEGIN
   radius := 3;
   WHILE radius<=7
      /*  Begin the commands to be executed.             */
   LOOP
    if radius mod 2!=0 then
    area := pi*power(radius,2);
    DBMS_OUTPUT.PUT_LINE(concat('Radius is:',radius));
    DBMS_OUTPUT.PUT_LINE(concat('Areas is:', area)); 
    end if;      
    radius := radius+1;
   END LOOP;
end;
/










<<<<<<<<<<<<<<<<<<<<<<<<[IF/ELSE]>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    v_gift varchar2(20);
    c_retailprice NUMBER(5,2) := 29.95;
BEGIN
    if c_retailprice > 56 THEN
        v_gift := 'FREE SHIPPING';
    ELSIF c_retailprice > 25 THEN
        v_gift := 'BOOKCOVER';
    ELSE
        v_gift := 'BOOKMARKER';
END IF;
DBMS_OUTPUT.PUT_LINE('THE gift for a book costing'||c_retailprice||'is a'||v_gift);
END;
/

<<<<<<<<<<<<<<<<<[BASIC LOOP]>>>>>>>>>>>>>>>>>   

DECLARE
    v_counter number(1) :=0;
BEGIN
    LOOP
        v_counter := v_counter+1;
        DBMS_OUTPUT.PUT_LINE('THE COURRENT value of the counter is: '|| v_counter);
        EXIT WHEN v_counter = 4;
    END LOOP;
END;
/


<<<<<<<<<<<<<<<<<<[FOR LOOP]>>>>>>>>>>>>>>>>>>>
SYSTAX:
FOR counter IN [reverse] lower_limit .. upper_limit
LOOP
sequence of statements;
END LOOP;



BEGIN
    FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('THE CURRENT VALUE OF THE COUNTER IS ' ||i);
    END LOOP;
END;
/





<<<<<<<<<<<<<<<[WHILE LOOP(if condition is true then exit)]>>>>>>>>>>>>>>
Syntax:

    WHILE condition LOOP
        Statements;
    END LOOP;



DECLARE
    v_counter NUMBER(2) := 0;
BEGIN
    WHILE v_counter < 15
        LOOP
    DBMS_OUTPUT.PUT_LINE('THE CURRENT VALUE OF THE COUNTER IS '|| v_counter);
    v_counter := v_counter + 1;
       END LOOP;
END;
/


<<<<<<<<<<<<<<<<<<<<<[INSERT to table using simple loop]>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant number(9,7) := 3.1415247;
    radius INTEGER(5);
    area NUMBER(14,2);
BEGIN
    radius := 3;
    LOOP
    area := pi*power(radius,2);
    INSERT INTO AREAS VALUES(radius, area);
    radius := radius+1;
    exit when area > 100;
end loop;
        DBMS_OUTPUT.PUT_LINE('Value Inserted');
end;
/

<<<<<<<<<<<<<<<<<<<<<[INSERT to table using for loop]>>>>>>>>>>>>>>>>>>>>>>


DECLARE
    pi constant number(9,7) := 3.1415162;
    radius INTEGER(5);
    area NUMBER(14,2);
    vcount number(3) default 0;
BEGIN
    for radius in 1..7
    LOOP
        area := pi*power(radius,2);
        vcount := vcount + 1;
        insert into areas values (radius, area);
    DBMS_OUTPUT.PUT_LINE('VALUE INSERTED: '||vcount);
    end LOOP;
    DBMS_OUTPUT.PUT_LINE('INSERTED SUCCESSFUL');
end;
/


<<<<<<<<<<<<<<<<<[INSERT to table using for loop(REVERSE)]>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.1415927;
    radius integer(5);
    area number(14,2);
BEGIN
    for radius in 1..7
    LOOP
    area := pi*power(radius,2);
    INSERT INTO AREAS VALUES(radius, area);
    DBMS_OUTPUT.PUT_LINE('Radius is: '||radius);
    END LOOP;
end;
/


<<<<<<<<<<<<<<<<<[Simple While LOOP]>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.1415166;
    radius INTEGER(5) default 0;
    area NUMBER(14,2) default 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('****************************************');
    radius := 3;
    while area<100
      LOOP
        area := pi*power(radius,2);
        DBMS_OUTPUT.PUT_LINE(concat('Radius is: ', radius));
        DBMS_OUTPUT.PUT_LINE(concat('Areas is: ', area));
        radius := radius+1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('BY:JUBAYER');
end;
/





<<<<<<<<<<<<<<<<<<<<<<<[Nested LOOPS]>>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    v_counter NUMBER(2) := 0;
BEGIN
    while v_counter < 3 LOOP
    FOR i IN 1 .. 2 LOOP
       DBMS_OUTPUT.PUT_LINE('THE courrent value of the for loop is '||i);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('The current value of the WHILE counter is '|| v_counter);
    v_counter := v_counter + 1;
     END LOOP;
END;
/











 <<<<<<<<<<<<<<<<<<<<<<<[CONTINUE]>>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
                  pi  constant NUMBER(9,7) := 3.1415927;
                 radius INTEGER(5);
                 area   NUMBER(14,2);
BEGIN
                radius := 0;
  LOOP
              radius := radius+1;
             continue when radius = 2;
             area := pi*power(radius,2);
             DBMS_OUTPUT.PUT_LINE(concat(radius, area));
  exit when area >100;
  END LOOP;
end;
/