Oracle PL/SQL – INSERT後のトリガーの例

CREATE TABLE USER__DETAILS
(
    USER__ID number(10) primary key,
    USER__NAME varchar2(15),
    EMAIL varchar2(20),
    PHONE number(12),
    PASSPORT__NO varchar2(10),
    DRIVING__LICENSE__NO varchar2(10)
);

CREATE TABLE USER__REMINDERS
(
    USER__ID number(10),
    REMINDER__TEXT varchar2(200),
    REMINDER__DATE date,
    STATUS varchar2(10)
);

CREATE OR REPLACE TRIGGER trg__after__insert__user
AFTER INSERT
  on USER__DETAILS
  FOR EACH ROW

DECLARE
counter number(2);
reminder__text varchar2(200);

BEGIN
counter := 0;
reminder__text := '';

  IF(:NEW.PASSPORT__NO = '' OR :NEW.PASSPORT__NO is null) THEN
    reminder__text := 'Please insert your passport details into system. ';
    counter := counter+1;
  END IF;

  IF(:NEW.DRIVING__LICENSE__NO = '' OR :NEW.DRIVING__LICENSE__NO is null) THEN
    reminder__text := reminder__text || 'Please insert your Driving license details into system.';
    counter := counter+1;
  END IF;

  -- If passport__no and/or driving__license__no is missing
  -- then counter will be >0 and below code will insert into user__reminders table.
  IF(counter>0) THEN
    INSERT INTO USER__REMINDERS VALUES (:NEW.USER__ID,reminder__text,sysdate+3,'PENDING');
  END IF;

END;

-- fire after insert trigger, no action.

INSERT INTO USER__DETAILS VALUES（1、 'USERNM1'、 'abcdxyz @ abc.com'、9999999999、 'PASSNUM123'、 'DRIVLIC999'）;

-- fire after insert trigger, password is null, insert new record into USER__REMINDERS
INSERT INTO USER__DETAILS VALUES (2,'USERNM22','[email protected]',1111111111,null,'LICNC12345');

-- fire after insert trigger, password and driving no are null, insert new record into USER__REMINDERS

INSERT INTO USER__DETAILS VALUES（3、 'USERNM33'、 'xyztttt @ abc.com'、3333333333、null、null）;

-- fire after insert trigger, driving no is null, insert new record into USER__REMINDERS
INSERT INTO USER__DETAILS VALUES (4,'USERNM44','[email protected]',4444444444,'ONLYPASS11',null);

select **  from USER__DETAILS;

select **  from USER__REMINDERS;