Hi All,
I need to create a trigger on a table which should trigger after each insert or update on the table.
SQL> create or replace
2 TRIGGER TBL_COMPONENT_XSL_TRG
3 BEFORE INSERT
4 OR UPDATE
ON TBL_COMPONENT_XSL_N
5 6 FOR EACH ROW
7 BEGIN
8 IF INSERTING
9 THEN
10 :NEW.hash_val := ora_hash(TBL_COMPONENT_XSL_N.COMPONENT_XSL.getClobVal());
11 ELSE
12 :NEW.hash_val := ora_hash(TBL_COMPONENT_XSL_N.COMPONENT_XSL.getClobVal());
13 END IF;
14 END;
15 /
Warning: Trigger created with compilation errors.
SQL> sho errors
Errors for TRIGGER TBL_COMPONENT_XSL_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/16 PL/SQL: Statement ignored
4/33 PLS-00201: identifier 'ORA_HASH' must be declared
6/16 PL/SQL: Statement ignored
6/33 PLS-00201: identifier 'ORA_HASH' must be declared
Whenever an insert or update happen on table TBL_COMPONENT_XSL, it should convert the xml column value to hash function and store the value in hash_val column.
SQL> desc TBL_COMPONENT_XSL_N
Name Null? Type
----------------------------------------- -------- ----------------------------
COMPONENT_XSL_ID NOT NULL NUMBER(18)
LINE_OF_BUSINESS NOT NULL VARCHAR2(30)
ORDER_SOURCE NOT NULL VARCHAR2(10)
COMPONENT_NAME NOT NULL VARCHAR2(30)
COMPONENT_SCHEMA_VERSION NOT NULL NUMBER(9)
ACCESS_LEVEL NOT NULL VARCHAR2(30)
COMPONENT_XSL SYS.XMLTYPE STORAGE BINARY
DESCRIPTION NOT NULL VARCHAR2(200)
HASH_VAL NUMBER
Appreciate any help.