Oracle Note 11, PL/SQL functions, and triggers

--Create a function

Create or Replace function Add_sal (ssal number)

       return number

Is

Begin

  if (Ssal >) Then

    return ssal + 51;

  elsif (Ssal >) Then

    return ssal + 111;

  Else

    return ssal + 222;

  End If;

End

Select Sal, Add_sal (SAL) from EMP;

--Trigger

CREATE TABLE Deptlog (

       UName varchar2 (20),

       Action Varchar2 (20),

       DTime Date

);

--Create a trigger

--for each row can trigger multiple entries, and how many times your language affects how many records will be triggered

Create or Replace Trigger TRIG_DEPT2

  After insert or delete or update on dept2/* for each row*/

Begin

  If inserting then

    INSERT into Deptlog values (user, ' Insert ', sysdate);

  elsif updating Then

    INSERT into Deptlog values (user, ' Update ', sysdate);

  elsif deleting Then

    INSERT into Deptlog values (user, ' delete ', sysdate);

End

SELECT * from Dept2;

SELECT * from Deptlog;

INSERT into DEPT2 values (, ' software ', ' CIC ');

Update dept2 Set loc = ' Go ' where deptno in (30);

Delete Dept2 where deptno = 55;

--Trigger

Create or Replace Trigger Trig_emp

  After update on dept for each row

Begin

  Update emp Set Emp.deptno =: New.deptno where Emp.deptno =: old.deptno;

End

Update Dept Set deptno = one     where deptno = 10;

SELECT * from EMP;

---statement-level triggers

Create or Replace Trigger TRG_INS_DEPT2

Before insert

On Dept2

Begin

     If user not in (' SCOTT ') then      

       Raise_application_error (-20001, ' only Scott can modify the table! ');

     End If;

End

disabling, enabling triggers

Alter TRIGGER tgr_name Disable | Enable

ALTER TABLE tableName Disable ALL Trigger | Enable all trigger;

Oracle Note 11, PL/SQL functions, and triggers