Oracle trigger exclude conditions-CodePudding

I am trying to create an Oracle trigger

CREATE OR REPLACE TRIGGER ACT_SESSION_AUDIT_TRIGGER
  AFTER INSERT OR UPDATE
  ON ACT_SESSION
  FOR EACH ROW
DECLARE
BEGIN
  if (:NEW.PROTOCOL='HTTP' or :NEW.PROTOCOL='SFTP') and :NEW.PRINCIPAL NOT IN ('AAA%', 'BBB%') then
    INSERT INTO ACT_SESSION_AUDIT (PRINCIPAL,
                                   CON_START_TIME,
                                   DIS_END_TIME,
                                   ADAPTER_NAME,
                                   ADAPTER_TYPE,
                                   PROTOCOL)
    VALUES(:NEW.PRINCIPAL,
           :NEW.CON_START_TIME,
           :NEW.DIS_END_TIME,
           :NEW.ADAPTER_NAME,
           :NEW.ADAPTER_TYPE,
           :NEW.PROTOCOL);
  END if;
END;

However when the PRINCIPAL begins with AAA, it causes the trigger to be invoked. What is the correct way of using NOT IN with partial strings ?

CodePudding user response：

You need LIKEfor the pattern matching in 'AAA%' and 'BBB%'.

IF :new.protocol IN ('HTTP', 'SFTP') AND
   :new.principal NOT LIKE 'AAA%' AND
   :new.principal NOT LIKE 'BBB%' THEN
  ...

You can move this to a WHEN cause by the way, to make this part of the trigger declaration part:

CREATE OR REPLACE TRIGGER ACT_SESSION_AUDIT_TRIGGER
  AFTER INSERT OR UPDATE
  ON act_session
  FOR EACH ROW
  WHEN (new.protocol IN ('HTTP', 'SFTP') AND
        new.principal NOT LIKE 'AAA%' AND
        new.principal NOT LIKE 'BBB%')
BEGIN

In theory this is faster, because the trigger won't get invoked, when the criteria is not met. I don't know, if this can really make a difference.