DBMS STANDARD

DBMS_STANDARD

DBMS_STANDARD package provides various sub programs that can be used without specifying the name of the package.

The procedures and functions comprising the DBMS_STANDARD package are in the following table below.

Procedures/Functions	Description
DELETING	Returns whether the trigger started from DELETE.
INSERTING	Returns whether the trigger started from INSERT.
UPDATING	Returns whether the trigger started from UPDATE.
UPDATING	Returns whether the trigger started from a specific column's UPDATE.

DELETING

Returns whether the trigger started from DELETE.

Syntax

BOOLEAN variable := DBMS_STANDARD.DELETING;
BOOLEAN variable := DELETING;

Result Value

If the trigger started from DELETE, TRUE is returned.

Exception

There is no exception.

Example

CREATE TABLE T1 (C1 INTEGER);
CREATE TABLE TMP ( C1 VARCHAR(10) );
INSERT INTO T1 VALUES(1);

CREATE OR REPLACE TRIGGER TRIG1
BEFORE DELETE ON T1
FOR EACH ROW
BEGIN
 IF DELETING THEN
  INSERT INTO TMP VALUES ('DELETE');
 END IF;
END;
/

iSQL> DELETE FROM T1;
1 row deleted.
iSQL> SELECT & FROM TMP;
1 row selected.

INSERTING

Returns whether the trigger started from INSERT.

Syntax

BOOLEAN variable := DBMS_STANDARD.INSERTING;
BOOLEAN variable := INSERTING;

Result Value

If the trigger started from INSERT, TRUE is returned.

Exception

There is no exception.

Example

CREATE TABLE T1 (C1 INTEGER);
CREATE TABLE TMP (C1 VARCHAR(10));

CREATE OR REPLACE TRIGGER TRIG1
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
 IF INSERTING THEN
  INSERT INTO TMP VALUES ('INSERT');
 END IF;
END;
/

iSQL> INSERT INTO T1 VALUES(2);
1 row inserted.
iSQL> SELECT * FROM TMP;
TMP.C1
--------------
INSERT
1 row selected.

UPDATING

Returns whether the trigger started from UPDATE.

Syntax

BOOLEAN variable := DBMS_STANDARD.UPDATING;
BOOLEAN variable := UPDATING;

Result Value

If the trigger started from UPDATE, TRUE is returned.

Exception

There is no exception.

Example

CREATE TABLE T1 (C1 INTEGER);
CREATE TABLE TMP (C1 VARCHAR(10));

INSERT INTO T1 VALUES(1);

CREATE OR REPLACE TRIGGER TRIG1
BEFORE UPDATE ON T1
FOR EACH ROW
BEGIN
 IF UPDATING THEN
  INSERT INTO TMP VALUES ('UPDATE');
 END IF;
END;
/

iSQL> UPDATE T1 SET C1 = 2;
1 row updated.
iSQL> SELECT * FROM TMP;
TMP.C1
--------------
UPDATE
1 row selected.

UPDATING (columnName)

Returns whether the trigger started from a specific column's UPDATE.

Syntax

BOOLEAN variable := DBMS_STANDARD.UPDATING(COLNAME IN VARCHAR(128));
BOOLEAN variable := UPDATING(COLNAME IN VARCHAR(128));

Parameter

Name	In/Output	Data Type	Description
COLNAME	IN	VARCHAR(128)	Specifies the name of the column

Result Value

If the trigger started from a specific coulmn's UPDATE, TRUE is returned.

Exception

There is no exception.

Example

CREATE TABLE T1 (C1 INTEGER, C2 INTEGER);
CREATE TABLE TMP (C1 VARCHAR(10));

INSERT INTO T1 VALUES(1, 2);

CREATE OR REPLACE TRIGGER TRIG1
BEFORE UPDATE ON T1
FOR EACH ROW
BEGIN
 IF UPDATING('C1') THEN
  INSERT INTO TMP VALUES ('UPDATE-C1');
ELSE
 INSERT INTO TMP VALUES ('OTHER');
 END IF;
END;
/

iSQL> UPDATE T1 SET C1 = 2;
1 row updated.
iSQL> SELECT * FROM TMP;
TMP.C1
--------------
UPDATE-C1
1 row selected.
iSQL> UPDATE T1 SET C2 = 3;
1 row updated.
iSQL> SELECT * FROM TMP;
TMP.C1
--------------
UPDATE-C1
OTHER
2 rows selected.