Which code can you use to ensure that the salary is not increased by more than 10% at a time nor
is it ever decreased?
A.
ALTER TABLE emp ADD
CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);
B.
CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal < old.sal OR
new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( – 20508, ‘Do not decrease
salary not increase by more than 10%’);
END;
C.
CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
WHEN (new.sal < old.sal OR
new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( – 20508, ‘Do not decrease
salary not increase by more than 10%’);
END;
D.
CREATE OR REPLACE TRIGGER check_sal
AFTER UPDATE OR sal ON emp
WHEN (new.sal < old.sal OR
-new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( – 20508, ‘Do not decrease
salary not increase by more than 10%’);
END;
Explanation:
Row triggers are the correct chose for solving the problem. A row trigger fires each time the
table is affected by the triggering event. If the triggering event affects no rows, a row trigger is not
executed.
Row triggers are useful if the trigger action depends on data of rows that are affected or on data
provided by the triggering event itself. You can create a BEFORE row trigger in order to prevent
the triggering operation from succeeding if a certain condition is violated.
Within a ROW trigger, reference the value of a column before and after the data change by
prefixing it with the OLD and NEW qualifier.
Incorrect Answers:
A: Check constaint can’t do this job lets take a look:SQL> ALTER TABLE emp ADD 2
CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1) 3 /Table altered.SQL> select
ename, sal 2 from emp 3 where ename = ‘KING’;ENAME SAL———- ———-KING 5000Now let’s
issue an update statementSQL> update emp 2 set sal = 10 3 where ename = ‘KING’;1 row
updated.As you can see the check constraint can’t compare the old value with the new value.
C: You can use NEW and OLD qualifier with row level triggers, If in the CREATE TRIGGER
statement you didn’t say FOR EACH ROW then the trigger will be statement level trigger
D: You can use NEW and OLD qualifier with row level triggers, If in the CREATE TRIGGER
statement you didn’t say FOR EACH ROW then the trigger will be statement level trigger