23.06.2006 10:18
OlegON
 
Предложено deucel
Код:
CREATE OR REPLACE TRIGGER smclientinfo_audt
   BEFORE INSERT OR UPDATE OR DELETE
   ON smclientinfo
   FOR EACH ROW
DECLARE
   tip     VARCHAR2 (6);
   suser   VARCHAR2 (30);
BEGIN
   IF     UPDATING
      AND (   :OLD.NAME != :NEW.NAME
           OR :OLD.accepted != :NEW.accepted
           OR :OLD.inn != :NEW.inn
          )
   THEN
      tip := 'UPDATE';

      SELECT surname
        INTO suser
        FROM smstaff
       WHERE serverlogin = USER;

      INSERT INTO smclientinfo_log
                  (smclientinfo_log.userh, smclientinfo_log.dateh,
                   smclientinfo_log.oper,
                   smclientinfo_log.hist
                  )
           VALUES (suser, SYSDATE,
                   tip,
                      'OLD:  '
                   || :OLD.ID
                   || ', '
                   || :OLD.NAME
                   || ', '
                   || :OLD.accepted
                   || ', '
                   || :OLD.inn
                  );

      INSERT INTO smclientinfo_log
                  (smclientinfo_log.userh, smclientinfo_log.dateh,
                   smclientinfo_log.oper,
                   smclientinfo_log.hist
                  )
           VALUES (suser, SYSDATE,
                   tip,
                      'NEW: '
                   || :NEW.ID
                   || ', '
                   || :NEW.NAME
                   || ', '
                   || :NEW.accepted
                   || ', '
                   || :NEW.inn
                  );
   ELSIF DELETING
   THEN
      tip := 'DELETE';

      SELECT surname
        INTO suser
        FROM smstaff
       WHERE serverlogin = USER;

      INSERT INTO smclientinfo_log
                  (smclientinfo_log.userh, smclientinfo_log.dateh,
                   smclientinfo_log.oper,
                   smclientinfo_log.hist
                  )
           VALUES (suser, SYSDATE,
                   tip,
                      'OLD:  '
                   || :OLD.ID
                   || ', '
                   || :OLD.NAME
                   || ', '
                   || :OLD.accepted
                   || ', '
                   || :OLD.inn
                  );
   ELSIF INSERTING
   THEN
      tip := 'INSERT';

      SELECT surname
        INTO suser
        FROM smstaff
       WHERE serverlogin = USER;

      INSERT INTO smclientinfo_log
                  (smclientinfo_log.userh, smclientinfo_log.dateh,
                   smclientinfo_log.oper,
                   smclientinfo_log.hist
                  )
           VALUES (suser, SYSDATE,
                   tip,
                      'NEW: '
                   || :NEW.ID
                   || ', '
                   || :NEW.NAME
                   || ', '
                   || :NEW.accepted
                   || ', '
                   || :NEW.inn
                  );
   END IF;
END smclientinfo_audt;
/
28.06.2006 10:49
OlegON
 
CREATE TABLE SMCLIENTINFO_LOG
(
USERH VARCHAR2(30 BYTE),
DATEH DATE,
OPER VARCHAR2(6 BYTE),
HIST VARCHAR2(255 BYTE)
)
Часовой пояс GMT +3, время: 02:23.

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.