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 = ‘Bill’;
ENAME SAL
———- ———-
Bill 5000
Now let’s issue an update statement
SQL> update emp
2 set sal = 10
3 where ename = ‘Bill’;
1 row updated.
As you can see the check constraint can’t compare the old value with the new value. D,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
B