Accessing Column Values:

When a trigger action or the trigger body contains statements which needed access to the table values or for checking the new value with the old value, two correlation names are used: that are - : new and: old. A: new refers to the new values entered in the trigger statement and A: old refers to the old existing value in the table. These are also known as pseudorecords because they do not hold any permanent record. In addition these can be providing only for row-level triggers. The subsequent table describes the values held through these with respect to the DML operations.

They are obtainable for both Before and After triggers.

Example

This instance uses the: new and displays the newly inserted record.

CREATE OR REPLACE TRIGGER IN_STR2 AFTER INSERT ON DEPT FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE(:NEW.DEPTNO||'    '||:NEW.DNAME||'     '||:NEW.LOC); END;

The output will display like:

INSERT INTO DEPT VALUES (12,'SALES','CHENNAI');

12        SALES            CHENNAI

1 row created.

The subsequent example checks for the deptno availability in the dept table. If corresponding deptno is not available in the table it displays a message as displays in the given instance.

CREATE OR REPLACE TRIGGER CHKDEPTNO BEFORE UPDATE OF DEPTNO ON EMP FOR EACH ROW-create a BEFORE trigger and update each row of department no. in employee table

DECLARE

OLD_DNO NUMBER;

BEGIN

SELECT DEPTNO INTO OLD_DNO FROM DEPT WHERE DEPTNO=:NEW.DEPTNO;--select the department no. from old department table

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('DEPTNO NOT AVAILABLE');-- show output

END;

The output of the program

UPDATE EMP SET DEPTNO=34 WHERE EMPNO=7900;-- update the department table

DEPTNO NOT AVAILABLE

1 row updated.