SQLPlus Create Tigger

An example to Create a Tigger

CREATE OR REPLACE TRIGGER customer_trigger
AFTER insert OR delete OR update
ON orders
FOR EACH ROW
DECLARE
changetype type.changetype%TYPE;
order orders%ROWTYPE;
name sales.name%TYPE;
updateViolation EXCEPTION;
nameNullViolation EXCEPTION;
BEGIN

IF INSERTING THEN
IF :NEW.name = 1 THEN
changetype := ‘insert’;
ELSE
changetype := ‘nothing’;
END IF;
order.order_id := :NEW.order_id;
order.name := :NEW.name;
ELSIF UPDATING THEN
IF :OLD.name != :NEW.name THEN
RAISE updateViolation;
END IF;
IF :OLD.name IS NULL OR :NEW.id IS NULL THEN
RAISE nameNullViolation;
END IF;
IF :OLD.name = 1 AND :NEW.name = 0 THEN
changetype := ‘delete’;
END IF;
IF :OLD.name = 0 AND :NEW.name = 1 THEN
changetype := ‘insert’;
END IF;
order.order_id := :NEW.order_id;
order.name := :NEW.name;
ELSIF DELETING THEN

END IF;
EXCEPTION

WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, ‘name not found.’);
WHEN updateViolation THEN
RAISE_APPLICATION_ERROR(-20000, ‘Updates faild.’);
WHEN nameNullViolation THEN
RAISE_APPLICATION_ERROR(-20000, ‘name have NULL value.’);

END;
/





Post your comment

*

code

 

 

See more website here...

  • Odlinks
  • Farsi-News
  • Advertise Here