How to write a statement level trigger in PL/SQL

Question

CREATE TABLE faculty
(f_id NUMBER(6),
f_last VARCHAR2(15),
f_first VARCHAR2(15),
f_mi CHAR(1),
loc_id NUMBER(5) not null,
f_phone VARCHAR2(10),
f_rank VARCHAR2(9),
f_salary number(9,2), 
f_super NUMBER(6), --supervisor/manager of the faculty member
f_pin NUMBER(4),
f_image BLOB, 
CONSTRAINT faculty_f_id_pk PRIMARY KEY(f_id),
CONSTRAINT faculty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id));

--- inserting records into FACULTY
INSERT INTO faculty VALUES
(1, 'Marx', 'Teresa', 'J', 9, '4075921695', 'Associate', 75000.00, 4, 6338, EMPTY_BLOB());

INSERT INTO faculty VALUES
(2, 'Zhulin', 'Mark', 'M', 10, '4073875682', 'Full', 98000.00, NULL, 1121, EMPTY_BLOB());

INSERT INTO faculty VALUES
(3, 'Langley', 'Colin', 'A', 12, '4075928719', 'Assistant', 60000.00, 4, 9871, EMPTY_BLOB());

INSERT INTO faculty VALUES
(4, 'Brown', 'Jonnel', 'D', 11, '4078101155', 'Full', 102000.00, NULL, 8297, EMPTY_BLOB());

INSERT INTO faculty VALUES
(5, 'Sealy', 'James', 'L', 13, '4079817153', 'Associate', 80000.00, 1, 6089, EMPTY_BLOB());

I wrote this ROW Level trigger(was below) already, but don't know how to get a statement level trigger which can do the same thing like this. And because cannot use :NEW / :OLD in statement level, then confused...

--Row Level
create or replace TRIGGER TRG_F_SALARY_CHECK
BEFORE INSERT OR UPDATE OF F_SUPER ON FACULTY
FOR EACH ROW

BEGIN
    IF :NEW.F_SUPER > :OLD.F_SUPER THEN
        :NEW.F_SALARY := :OLD.F_SALARY + 2000*(:NEW.F_SUPER - 5);
        DBMS_OUTPUT.PUT_LINE('The salary has been updated');
    END IF;
    IF :NEW.F_SUPER < :OLD.F_SUPER THEN
        IF :OLD.F_SUPER >=5 THEN
            IF :NEW.F_SUPER < 5 THEN
                :NEW.F_SALARY := :OLD.F_SALARY - 2000*(:OLD.F_SUPER - 5);
                DBMS_OUTPUT.PUT_LINE('The salary has been updated');
            ELSE
              :NEW.F_SALARY := :OLD.F_SALARY - 2000*(:OLD.F_SUPER - :NEW.F_SUPER);
              DBMS_OUTPUT.PUT_LINE('The salary has been updated');
            END IF;
        END IF;
    END IF;
END;

create or replace TRIGGER TRG_F_SALARY_CHECK_STATEMENT
BEFORE INSERT OR UPDATE OF F_SUPER, F_SALARY ON FACULTY
DECLARE
  --  OLD_F_SUPER NUMBER(10);
    OLD_F_SALARY NUMBER(9);
BEGIN
--        SELECT F_SUPER INTO OLD_F_SUPER FROM FACULTY;
--       SELECT SUM(F_SALARY) INTO OLD_F_SALARY FROM FACULTY
--       WHERE F_ID;
   UPDATE FACULTY
   /* SET F_SALARY = F_SALARY + 2000*(F_SUPER -5)
        WHERE F_SUPER > 5;*/
        SET F_SALARY = OLD_F_SALARY + 2000*(F_SUPER - 5)
            WHERE F_SUPER > 5;
END;

btw, table FACULTY has including in F_ID, F_SALARY, F_SUPER, etc.. I am going to get ** one-row trigger**(already done) and one statement trigger for updating the salary of faculty with changing of F_SUPER number. For every single F_SUPER change(increase or decrease) after 5, F_SALARY will change(increase or decrease) for $2000.

The test code is below:

/*F_ID = 5, F_SUPER = 7, F_SALARY = 84000*/
UPDATE FACULTY
SET F_SUPER = 7
WHERE F_ID = 5;
/*F_ID =5, F_SUPER = 2, F_SALARY = 80000*/ 
UPDATE FACULTY
SET F_SUPER = 2
WHERE F_ID = 5;

Thanks

Answer 1

He wants to update the whole table (with filter) with a statement level trigger and IMO the trigger should be with "after insert or update for".

Answer 2

I wrote this ROW Level trigger(was below) already, but don't know how to get a statement level trigger which can do the same thing like this. And because cannot use :NEW / :OLD in statement level, then confused...

Statement level trigger and row level triggers are not the same. They are provided by Oracle depending on the usage. Hence all row level triggers cannot be replaced with a statement level triggers. See below definition and usage:

Row-level triggers for data-related activities

• Row-level triggers execute once for each row in a transaction.

• Row-level triggers are the most common type of triggers; they are often used in data auditing applications.

• Row-level trigger is identified by the FOR EACH ROW clause in the CREATE TRIGGER command.

Statement-level triggers for transaction-related activities

• Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into the Customer table, then a statement-level trigger on that table would only be executed once.

• Statement-level triggers therefore are not often used for data-related activities; they are normally used to enforce additional security measures on the types of transactions that may be performed on a table.

• Statement-level triggers are the default type of triggers created and are identified by omitting the FOR EACH ROW clause in the CREATE TRIGGER command.

Example of Statement Level Trigger:

CREATE or REPLACE TRIGGER Before_Update_Stat_product 
BEFORE UPDATE ON product 
Begin 
  INSERT INTO table  
  Values('Before update, statement level',sysdate); 
END; 
/