create or replace TRIGGER "PIDS_ADMIN_PH2"."A_CLINIC_PRODUCT_LOCN_LOG" AFTER INSERT OR DELETE OR UPDATE ON "T_CLINIC_PRODUCT_BATCH_LOCN" REFERENCING FOR EACH ROW DECLARE
Time_now T_CLINIC_PRODUCT_LOCN_LOG.LOG_TIME%TYPE;
Terminal T_CLINIC_PRODUCT_LOCN_LOG.LOG_USER%TYPE;
NewValue T_CLINIC_PRODUCT_LOCN_LOG.BEFORE%TYPE;
OldValue T_CLINIC_PRODUCT_LOCN_LOG.AFTER%TYPE;
N_KeyVal T_CLINIC_PRODUCT_LOCN_LOG.KEY%TYPE;
O_KeyVal T_CLINIC_PRODUCT_LOCN_LOG.KEY%TYPE;
BEGIN
NewValue := '';
OldValue := '';
Time_now := SYSDATE;
Terminal := USERENV('TERMINAL');
N_KeyVal := 'UUID=' || NVL(:new.UUID, '');
O_KeyVal := 'UUID=' || NVL(:old.UUID, '');
IF INSERTING THEN
NewValue := 'UUID=' || nvl(:new.UUID,'') || ',' || chr(10)
|| 'CLINIC_PRODUCT_BATCH_ID='||nvl(:new.CLINIC_PRODUCT_BATCH_ID,'') || ',' || chr(10)
|| 'PREPACKING_ID='||nvl(:new.PREPACKING_ID,'') || ',' || chr(10)
|| 'LOCATION_ID='||nvl(:new.LOCATION_ID,'') || ',' || chr(10)
|| 'NO_UNITS='||nvl(:new.NO_UNITS,'') || ',' || chr(10)
|| 'NO_UNITS_ON_STOCK_TAKE_DATE='||nvl(:new.NO_UNITS_ON_STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'STOCK_TAKE_DATE='||nvl(:new.STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'UPDATED_ID='||nvl(:new.UPDATED_ID,'') || ',' || chr(10)
|| 'UPDATED_DATE='||nvl(:new.UPDATED_DATE,'') || ',' || chr(10)
|| 'CREATED_ID='||nvl(:new.CREATED_ID,'') || ',' || chr(10)
|| 'CREATED_DATE='||nvl(:new.CREATED_DATE,'') || ',' || chr(10)
|| 'ACTIVE_STATUS='||nvl(:new.ACTIVE_STATUS,'') || ',' || chr(10)
;
INSERT INTO T_CLINIC_PRODUCT_LOCN_LOG (LOG_ID, LOG_TABLE, LOG_TIME, LOG_USER, TYPE, BEFORE, AFTER, KEY)
VALUES (S_CLINIC_PRODUCT_LOCN_LOG.NEXTVAL, 'T_CLINIC_PRODUCT_BATCH_LOCN', Time_now, nvl(:new.UPDATED_ID, Terminal), 'I', NULL, SUBSTR(NewValue,1,length(NewValue)-1), N_KeyVal);
END;
Time_now T_CLINIC_PRODUCT_LOCN_LOG.LOG_TIME%TYPE;
Terminal T_CLINIC_PRODUCT_LOCN_LOG.LOG_USER%TYPE;
NewValue T_CLINIC_PRODUCT_LOCN_LOG.BEFORE%TYPE;
OldValue T_CLINIC_PRODUCT_LOCN_LOG.AFTER%TYPE;
N_KeyVal T_CLINIC_PRODUCT_LOCN_LOG.KEY%TYPE;
O_KeyVal T_CLINIC_PRODUCT_LOCN_LOG.KEY%TYPE;
BEGIN
NewValue := '';
OldValue := '';
Time_now := SYSDATE;
Terminal := USERENV('TERMINAL');
N_KeyVal := 'UUID=' || NVL(:new.UUID, '');
O_KeyVal := 'UUID=' || NVL(:old.UUID, '');
IF INSERTING THEN
NewValue := 'UUID=' || nvl(:new.UUID,'') || ',' || chr(10)
|| 'CLINIC_PRODUCT_BATCH_ID='||nvl(:new.CLINIC_PRODUCT_BATCH_ID,'') || ',' || chr(10)
|| 'PREPACKING_ID='||nvl(:new.PREPACKING_ID,'') || ',' || chr(10)
|| 'LOCATION_ID='||nvl(:new.LOCATION_ID,'') || ',' || chr(10)
|| 'NO_UNITS='||nvl(:new.NO_UNITS,'') || ',' || chr(10)
|| 'NO_UNITS_ON_STOCK_TAKE_DATE='||nvl(:new.NO_UNITS_ON_STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'STOCK_TAKE_DATE='||nvl(:new.STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'UPDATED_ID='||nvl(:new.UPDATED_ID,'') || ',' || chr(10)
|| 'UPDATED_DATE='||nvl(:new.UPDATED_DATE,'') || ',' || chr(10)
|| 'CREATED_ID='||nvl(:new.CREATED_ID,'') || ',' || chr(10)
|| 'CREATED_DATE='||nvl(:new.CREATED_DATE,'') || ',' || chr(10)
|| 'ACTIVE_STATUS='||nvl(:new.ACTIVE_STATUS,'') || ',' || chr(10)
;
INSERT INTO T_CLINIC_PRODUCT_LOCN_LOG (LOG_ID, LOG_TABLE, LOG_TIME, LOG_USER, TYPE, BEFORE, AFTER, KEY)
VALUES (S_CLINIC_PRODUCT_LOCN_LOG.NEXTVAL, 'T_CLINIC_PRODUCT_BATCH_LOCN', Time_now, nvl(:new.UPDATED_ID, Terminal), 'I', NULL, SUBSTR(NewValue,1,length(NewValue)-1), N_KeyVal);
END;
解决方案 »
- .Net连接Oracle如何配置tnsnames.ora
- 求oracle10g的热备份的实施方案
- 急,Enterprise Manager 无法连接到数据库实例。下面列出了组件的状态
- ORACLE 如何获取当前时间的上一天的日期?
- 插入数据乱码问题,急
- 傻傻的问,如何进入isql*plus,是i不是原来那个sqlplus
- 请教:job为何能手动RUN而不能按照时间间隔自动执行(如每分钟执行一次),(在线等)
- ORA-03113 end-of-file on communication channel是怎么回事? 急!
- ■■高手救命!字段是Date类型,值是2002/05/05,我要传入某个日期数据例如2002/10/30,在SQL语句中如何求差几天???
- oracle存储过程同步表数据,求帮助
- Oracle使用SYS用户SYSDBA权限恢复数据到orcl表空间是EP,新创建的ep用户默认表空间是EP无法访问数据表
- 求助,千万级数据更新!!!!
N_KeyVal := 'UUID=' || NVL(:new.UUID, '');
O_KeyVal := 'UUID=' || NVL(:old.UUID, '');
Time_now T_CLINIC_PRODUCT_LOCN_LOG.LOG_TIME%TYPE;
Terminal T_CLINIC_PRODUCT_LOCN_LOG.LOG_USER%TYPE;
NewValue T_CLINIC_PRODUCT_LOCN_LOG.BEFORE%TYPE;
OldValue T_CLINIC_PRODUCT_LOCN_LOG.AFTER%TYPE;
N_KeyVal T_CLINIC_PRODUCT_LOCN_LOG.KEY%TYPE;
O_KeyVal T_CLINIC_PRODUCT_LOCN_LOG.KEY%TYPE;BEGIN
NewValue := '';
OldValue := '';
Time_now := SYSDATE;
Terminal := USERENV('TERMINAL');
N_KeyVal := 'UUID=' || NVL(:new.UUID, '');
O_KeyVal := 'UUID=' || NVL(:old.UUID, '');IF INSERTING THEN
NewValue := 'UUID=' || nvl(:new.UUID,'') || ',' || chr(10)
|| 'CLINIC_PRODUCT_BATCH_ID='||nvl(:new.CLINIC_PRODUCT_BATCH_ID,'') || ',' || chr(10)
|| 'PREPACKING_ID='||nvl(:new.PREPACKING_ID,'') || ',' || chr(10)
|| 'LOCATION_ID='||nvl(:new.LOCATION_ID,'') || ',' || chr(10)
|| 'NO_UNITS='||nvl(:new.NO_UNITS,'') || ',' || chr(10)
|| 'NO_UNITS_ON_STOCK_TAKE_DATE='||nvl(:new.NO_UNITS_ON_STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'STOCK_TAKE_DATE='||nvl(:new.STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'UPDATED_ID='||nvl(:new.UPDATED_ID,'') || ',' || chr(10)
|| 'UPDATED_DATE='||nvl(:new.UPDATED_DATE,'') || ',' || chr(10)
|| 'CREATED_ID='||nvl(:new.CREATED_ID,'') || ',' || chr(10)
|| 'CREATED_DATE='||nvl(:new.CREATED_DATE,'') || ',' || chr(10)
|| 'ACTIVE_STATUS='||nvl(:new.ACTIVE_STATUS,'') || ',' || chr(10)
;
INSERT INTO T_CLINIC_PRODUCT_LOCN_LOG (LOG_ID, LOG_TABLE, LOG_TIME, LOG_USER, TYPE, BEFORE, AFTER, KEY)
VALUES (S_CLINIC_PRODUCT_LOCN_LOG.NEXTVAL, 'T_CLINIC_PRODUCT_BATCH_LOCN', Time_now, nvl(:new.UPDATED_ID, Terminal), 'I', NULL, SUBSTR(NewValue,1,length(NewValue)-1), N_KeyVal);ELSIF DELETING THEN
OldValue := 'UUID=' || nvl(:old.UUID,'') || ',' || chr(10)
|| 'CLINIC_PRODUCT_BATCH_ID='||nvl(:old.CLINIC_PRODUCT_BATCH_ID,'') || ',' || chr(10)
|| 'PREPACKING_ID='||nvl(:old.PREPACKING_ID,'') || ',' || chr(10)
|| 'LOCATION_ID='||nvl(:old.LOCATION_ID,'') || ',' || chr(10)
|| 'NO_UNITS='||nvl(:old.NO_UNITS,'') || ',' || chr(10)
|| 'NO_UNITS_ON_STOCK_TAKE_DATE='||nvl(:old.NO_UNITS_ON_STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'STOCK_TAKE_DATE='||nvl(:old.STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'UPDATED_ID='||nvl(:old.UPDATED_ID,'') || ',' || chr(10)
|| 'UPDATED_DATE='||nvl(:old.UPDATED_DATE,'') || ',' || chr(10)
|| 'CREATED_ID='||nvl(:old.CREATED_ID,'') || ',' || chr(10)
|| 'CREATED_DATE='||nvl(:old.CREATED_DATE,'') || ',' || chr(10)
|| 'ACTIVE_STATUS='||nvl(:old.ACTIVE_STATUS,'') || ',' || chr(10)
;
INSERT INTO T_AUDIT_LOG (LOG_ID, LOG_TABLE, LOG_TIME, LOG_USER, TYPE, BEFORE, AFTER, KEY)
VALUES (S_CLINIC_PRODUCT_LOCN_LOG.NEXTVAL, 'T_CLINIC_PRODUCT_BATCH_LOCN', Time_now, nvl(:old.UPDATED_ID, Terminal), 'D', SUBSTR(OldValue,1,length(OldValue)-1), NULL, O_KeyVal);ELSE
IF (:old.UUID <> :new.UUID) or (:old.UUID is null and :new.UUID is not null) or (:old.UUID is not null and :new.UUID is null) THEN
NewValue := NewValue || 'UUID=' || NVL(:new.UUID,'') || ',' || chr(10);
OldValue := OldValue || 'UUID=' || NVL(:old.UUID,'') || ',' || chr(10);
END IF;
IF (:old.CLINIC_PRODUCT_BATCH_ID <> :new.CLINIC_PRODUCT_BATCH_ID) or (:old.CLINIC_PRODUCT_BATCH_ID is null and :new.CLINIC_PRODUCT_BATCH_ID is not null) or (:old.CLINIC_PRODUCT_BATCH_ID is not null and :new.CLINIC_PRODUCT_BATCH_ID is null) THEN
NewValue := NewValue || 'CLINIC_PRODUCT_BATCH_ID=' || NVL(:new.CLINIC_PRODUCT_BATCH_ID,'') || ',' || chr(10);
OldValue := OldValue || 'CLINIC_PRODUCT_BATCH_ID=' || NVL(:old.CLINIC_PRODUCT_BATCH_ID,'') || ',' || chr(10);
END IF;
IF (:old.PREPACKING_ID <> :new.PREPACKING_ID) or (:old.PREPACKING_ID is null and :new.PREPACKING_ID is not null) or (:old.PREPACKING_ID is not null and :new.PREPACKING_ID is null) THEN
NewValue := NewValue || 'PREPACKING_ID=' || NVL(:new.PREPACKING_ID,'') || ',' || chr(10);
OldValue := OldValue || 'PREPACKING_ID=' || NVL(:old.PREPACKING_ID,'') || ',' || chr(10);
END IF;
IF (:old.LOCATION_ID <> :new.LOCATION_ID) or (:old.LOCATION_ID is null and :new.LOCATION_ID is not null) or (:old.LOCATION_ID is not null and :new.LOCATION_ID is null) THEN
NewValue := NewValue || 'LOCATION_ID=' || NVL(:new.LOCATION_ID,'') || ',' || chr(10);
OldValue := OldValue || 'LOCATION_ID=' || NVL(:old.LOCATION_ID,'') || ',' || chr(10);
END IF;
IF (:old.NO_UNITS <> :new.NO_UNITS) or (:old.NO_UNITS is null and :new.NO_UNITS is not null) or (:old.NO_UNITS is not null and :new.NO_UNITS is null) THEN
NewValue := NewValue || 'NO_UNITS=' || NVL(:new.NO_UNITS,'') || ',' || chr(10);
OldValue := OldValue || 'NO_UNITS=' || NVL(:old.NO_UNITS,'') || ',' || chr(10);
END IF;
IF (:old.NO_UNITS_ON_STOCK_TAKE_DATE <> :new.NO_UNITS_ON_STOCK_TAKE_DATE) or (:old.NO_UNITS_ON_STOCK_TAKE_DATE is null and :new.NO_UNITS_ON_STOCK_TAKE_DATE is not null) or (:old.NO_UNITS_ON_STOCK_TAKE_DATE is not null and :new.NO_UNITS_ON_STOCK_TAKE_DATE is null) THEN
NewValue := NewValue || 'NO_UNITS_ON_STOCK_TAKE_DATE=' || NVL(:new.NO_UNITS_ON_STOCK_TAKE_DATE,'') || ',' || chr(10);
OldValue := OldValue || 'NO_UNITS_ON_STOCK_TAKE_DATE=' || NVL(:old.NO_UNITS_ON_STOCK_TAKE_DATE,'') || ',' || chr(10);
END IF;
IF (:old.STOCK_TAKE_DATE <> :new.STOCK_TAKE_DATE) or (:old.STOCK_TAKE_DATE is null and :new.STOCK_TAKE_DATE is not null) or (:old.STOCK_TAKE_DATE is not null and :new.STOCK_TAKE_DATE is null) THEN
NewValue := NewValue || 'STOCK_TAKE_DATE=' || NVL(:new.STOCK_TAKE_DATE,'') || ',' || chr(10);
OldValue := OldValue || 'STOCK_TAKE_DATE=' || NVL(:old.STOCK_TAKE_DATE,'') || ',' || chr(10);
END IF;
IF (:old.UPDATED_ID <> :new.UPDATED_ID) or (:old.UPDATED_ID is null and :new.UPDATED_ID is not null) or (:old.UPDATED_ID is not null and :new.UPDATED_ID is null) THEN
NewValue := NewValue || 'UPDATED_ID=' || NVL(:new.UPDATED_ID,'') || ',' || chr(10);
OldValue := OldValue || 'UPDATED_ID=' || NVL(:old.UPDATED_ID,'') || ',' || chr(10);
END IF;
IF (:old.UPDATED_DATE <> :new.UPDATED_DATE) or (:old.UPDATED_DATE is null and :new.UPDATED_DATE is not null) or (:old.UPDATED_DATE is not null and :new.UPDATED_DATE is null) THEN
NewValue := NewValue || 'UPDATED_DATE=' || NVL(:new.UPDATED_DATE,'') || ',' || chr(10);
OldValue := OldValue || 'UPDATED_DATE=' || NVL(:old.UPDATED_DATE,'') || ',' || chr(10);
END IF;
IF (:old.CREATED_ID <> :new.CREATED_ID) or (:old.CREATED_ID is null and :new.CREATED_ID is not null) or (:old.CREATED_ID is not null and :new.CREATED_ID is null) THEN
NewValue := NewValue || 'CREATED_ID=' || NVL(:new.CREATED_ID,'') || ',' || chr(10);
OldValue := OldValue || 'CREATED_ID=' || NVL(:old.CREATED_ID,'') || ',' || chr(10);
END IF;
IF (:old.CREATED_DATE <> :new.CREATED_DATE) or (:old.CREATED_DATE is null and :new.CREATED_DATE is not null) or (:old.CREATED_DATE is not null and :new.CREATED_DATE is null) THEN
NewValue := NewValue || 'CREATED_DATE=' || NVL(:new.CREATED_DATE,'') || ',' || chr(10);
OldValue := OldValue || 'CREATED_DATE=' || NVL(:old.CREATED_DATE,'') || ',' || chr(10);
END IF;
IF (:old.ACTIVE_STATUS <> :new.ACTIVE_STATUS) or (:old.ACTIVE_STATUS is null and :new.ACTIVE_STATUS is not null) or (:old.ACTIVE_STATUS is not null and :new.ACTIVE_STATUS is null) THEN
NewValue := NewValue || 'ACTIVE_STATUS=' || NVL(:new.ACTIVE_STATUS,'') || ',' || chr(10);
OldValue := OldValue || 'ACTIVE_STATUS=' || NVL(:old.ACTIVE_STATUS,'') || ',' || chr(10);
END IF;INSERT INTO T_CLINIC_PRODUCT_BATCH_LOCN (LOG_ID, LOG_TABLE, LOG_TIME, LOG_USER, TYPE, BEFORE, AFTER, KEY)
VALUES (S_CLINIC_PRODUCT_LOCN_LOG.NEXTVAL, 'T_CLINIC_PRODUCT_BATCH_LOCN', Time_now, NVL(:new.UPDATED_ID,Terminal), 'U', SUBSTR(OldValue,1,length(OldValue)-1), SUBSTR(NewValue,1,length(NewValue)-1), N_KeyVal);
END IF;
END;