CODEWITHSUNDEEP
sqltriggers
An SO User
An SO User

Reputation: 24998

Writing a simple trigger

This is self-study and not homework so don't hasten to to hit the -1 button.

I am trying to learn different types of triggers that are a part of PL-SQL. I understand the syntax and the logic and how they work. However, I have a difficulty in implementing them.

I have a very simple table of employees that has just three columns namely the id, name and salary. The table is populated with data. The idea is that if you update the salary of the employee you can only increase it and not decrease it.

I managed to get this far: 

create or replace trigger check_salary
before update of sal
on emp_info
for each row
begin
    // something , something
end;
/

but what will be the body of the trigger is a mystery to me. Will someone please explain to me how that can be done ?

Logically, you will first check the value that has been entered. Then you compare it with the existing value. Only if the value passed is greater than the current value of salary should that row be updated. Else, it should not be.

My source of study: http://plsql-tutorial.com/plsql-triggers.htm

Upvotes: 1

Views: 98

Answers (2)

Mureinik
Mureinik

Reputation: 311188

Basically, you'd want to check if the new salary is larger than the old one. If it is - you do nothing, this is a valid update according to the rules you've specified. If it isn't, you should raise an error to indicate that this is an invalid update.

create or replace trigger check_salary
before update of sal
on emp_info
for each row
begin
   if :new.sal < :old.sal then
        raise_application_exception(-20001, 'sal cannot be decreased!');
   end if;
end;
/

Upvotes: 1

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You have 3 possibilites:

  1. Check the new value, if it is lower, than raise_application_exception(-20001, 'Salaray can not be lower than existing value.') which will disallow update of the record.
  2. If the value is lower, then assign existing value to the new value: :new.salary := :old.salary (bad idea, because it may be hard to understand why a value is not being updated without looking at the trigger).
  3. You could create a VIEW based on the table and create an INSTEAD OF trigger on that view in which you would check if the base table should be updated or not.

Upvotes: 1

Related Questions