create a PL/SQL trigger which implement check constraint i.e commission of an employ is never greater than salary?

by mukesh
(patna)

How do I create an Oracle PL/SQL trigger which implements check constraint i.e commission of an employee is never greater than salary?

Comments for create a PL/SQL trigger which implement check constraint i.e commission of an employ is never greater than salary?

Average Rating starstarstarstarstar

Click here to add your own comments

Feb 27, 2015
Rating
starstarstarstarstar
Creating triggers in PL/SQL
by: Anonymous

You could implement this as a trigger that is fired when the table is updated or a row is inserted quite easily. Here is an example:

CREATE OR REPLACE TRIGGER emp_commsn_ck BEFORE UPDATE OR INSERT ON EMP
FOR EACH ROW
BEGIN

IF :NEW.commission > :NEW.salary THEN RAISE ; END IF;

END;

Where is the name of teh exception you wnat to raise should this condition occur.

Then when you run an insert statement like this

INSERT INTO EMP (emp_id, emp_name, salary, commission) VALUES (99999,'JOE BLOGGS',66,99);

your exception will be raised and can be trapped by the application so that a helpful error message can be displayed to the user.

However you don't need to write a triiger to do this at all as the functionality is already built into Oracle. All you need is a check constraint on the table which can be implemented when the table is created or afterwards with an "alter table" statement like this:

ALTER TABLE emp ADD CONSTRAINT chk_cmmsn CHECK (commission <= salary);

Then when you run an insert statement like this

INSERT INTO EMP (emp_id, emp_name, salary, commission) VALUES (99999,'JOE BLOGGS',66,99);

Oracle will raise an exception telling you that the check constraint has been violated. Again this can be trapped by the application in order to pass a suitable message back to the user.

Remember, always use Oracle's built-in functionality first before you start to write your own. That will save you a lot of time, money and effort and will ensure your Oracle applications are scalable.

The following questions and answers on triggers may also be useful

What are PL/SQL triggers? at http://www.asktheoracle.net/what-are-plsql-triggers.html
using trigger on one table to update another table in Oracle - see http://www.asktheoracle.net/using-trigger-on-one-table-to-update-another-table-in-oracle.html
What's the difference between a trigger and a procedure? See http://www.asktheoracle.net/whats-the-difference-between-a-trigger-and-a-procedure-in-oracle-plsql.html
What's the difference between a trigger and a constraint in Oracle? http://www.asktheoracle.net/whats-the-difference-between-a-trigger-and-a-constraint-in-oracle.html
Which process is inserting a record in a table? See http://www.asktheoracle.net/which-process-is-inserting-a-record-in-a-table.html
stop trigger - http://www.asktheoracle.net/stop-trigger.html

Also see our SQL tutorials at http://www.asktheoracle.net/oracle-sql-tutorial.html and our PL/SQL tutorials at http://www.asktheoracle.net/plsql-tutorial.html for more help.

For information about our classroom quality online Oracle training courses see http://www.asktheoracle.net/oracle-training.html

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.