oracle 使用触发器监控表旧值

oracle 使用触发器监控表旧值


CREATE OR REPLACE TRIGGER delete_tbltest
BEFORE DELETE ON dbo.tbltest
FOR EACH ROW
DECLARE
-- local variables here
BEGIN

return;

/* INSERT INTO dbo_audit_ddl
(audit_id,
ddl_time,
session_id,
ddl_type,
os_user,
ip_address,
terminal,
host,
user_name,
sql_text,
program,
machine,
object_type)
SELECT seq_dbo_audit_ddl.nextval,
SYSDATE,
s.sid,
s.serial#,
s.osuser,
sys_context('userenv', 'ip_address'),
s.terminal,
sys_context('USERENV', 'HOST'),
s.username,
q.sql_text line,
s.program,
s.machine,
'DELETE'
FROM v$sql q, v$session s
WHERE s.audsid = (SELECT userenv('SESSIONID') FROM dual)
AND s.prev_sql_addr = q.address
AND s.prev_hash_value = q.hash_value;*/

INSERT INTO tbltest_bak
(

orgid,
mocode,
moname,
itemcode,
TYPE,
status,
planstartdate,
planenddate,
actstartdate,
actenddate,
orderid,
orderlineid,
memo,
pendingcause,
cancelcause,
shortcause,
moclass,
inventory,
parentmocode,
molevel,
producttype,
iscompleted,
planqty,
limitqty,
finishqty,
checkqty,
shortqty,
actqty,
ngqty,
islimit,
cuser,
cdate,
ctime,
muser,
mdate,
mtime,
eattribute1,
eattribute2,
eattribute3,
eattribute4,
eattribute5,
eattribute6,
eattribute7,
eattribute8,
eattribute9,
eattribute10,
forecast_qty,
mosubtype,
ecnnumber,
mover,
mbver,
moinputqty,
idmergerule,
erpsubinvcode,
uploadstatus,
uploadqty,
uploadend,
process_date,
process_flag,
finalmocode,
moscrapqty,
segcode,
faccode,
closeddate,
pi)
VALUES
(:old.orgid,
:old.mocode,
:old.moname,
:old.itemcode,
:old.type,
:old.status,
:old.planstartdate,
:old.planenddate,
:old.actstartdate,
:old.actenddate,
:old.orderid,
:old.orderlineid,
:old.memo,
:old.pendingcause,
:old.cancelcause,
:old.shortcause,
:old.moclass,
:old.inventory,
:old.parentmocode,
:old.molevel,
:old.producttype,
:old.iscompleted,
:old.planqty,
:old.limitqty,
:old.finishqty,
:old.checkqty,
:old.shortqty,
:old.actqty,
:old.ngqty,
:old.islimit,
:old.cuser,
:old.cdate,
:old.ctime,
:old.muser,
:old.mdate,
:old.mtime,
:old.eattribute1,
:old.eattribute2,
:old.eattribute3,
:old.eattribute4,
:old.eattribute5,
:old.eattribute6,
:old.eattribute7,
:old.eattribute8,
:old.eattribute9,
:old.eattribute10,
:old.forecast_qty,
:old.mosubtype,
:old.ecnnumber,
:old.mover,
:old.mbver,
:old.moinputqty,
:old.idmergerule,
:old.erpsubinvcode,
:old.uploadstatus,
:old.uploadqty,
:old.uploadend,
:old.process_date,
:old.process_flag,
:old.finalmocode,
:old.moscrapqty,
:old.segcode,
:old.faccode,
:old.closeddate,
:old.pi);
INSERT INTO dbo_audit_ddl
(audit_id,
ddl_time,
session_id,
os_user,
ip_address,
terminal,
host,
user_name,
ddl_type,
object_type,
owner,
object_name,
sql_text,
program,
machine)
SELECT seq_dbo_audit_ddl.nextval,
SYSDATE,
sys_context('USERENV', 'SESSIONID'),
sys_context('USERENV', 'OS_USER'),
sys_context('USERENV', 'IP_ADDRESS'),
sys_context('USERENV', 'TERMINAL'),
sys_context('USERENV', 'HOST'),
ora_login_user,
ora_sysevent,
ora_dict_obj_type,
:old.orgid,
:old.mocode,
'delete_tbltest',
v.program,
v.machine
FROM v$session v
WHERE v.audsid = sys_context('USERENV', 'SESSIONID');
END delete_tblteststep;

博文最后更新时间:


评论

  • Merziuz

    U428I0 http://pills2sale.com/ viagra online

  • Fatima

    Thank you for the auspicious writeup. It in fact was a amusement account it. https://aklexterminateur.com

  • Dewey

    I'll put her on <a href="https://conference.ue-varna.bg/emc/kraftmaid-coreguard-sink-base-4yov">preo de corega tabs</a> HBO Sports is celebrating the anniversary with a new documentary in its "Legendary Nights" series, the "Tale of Gatti-Ward." It debuts Saturday night following the network's coverage of a junior welterweight fight between Mike Alvarado and Ruslan Provodnikov.

  • Donny

    We'd like to invite you for an interview <a href="http://thewhitedogbistro.com/precio-aspirina-protect-san-pablo-3uxv">aspirine du rhone prix </a> Some analysts say North Korea takes Kaesong's resumption seriously because it believes it could help draw outside investment and revive its struggling economy, one of leader Kim Jong Un's top stated goals, along with nuclear bomb production. The park was a rare, legitimate source of hard currency for North Korea.

  • Alvin

    Could I have an application form? <a href="http://us.webpage-demo.com/communityschools/illumibowl-walmart-jg3u">illumibowl walmart </a> The FTSE 100 was up 56.77 points, or 0.9 percent, at6,487.26 points by 1417 GMT, building on a 1.5 percent jump theprevious session and hitting its highest levels since the U.S.government went into shutdown at the start of this month.

  • Elroy

    Free medical insurance <a href="http://magnocentrojoyero.com/blog/clindamycin-tablet-india-upwe">clindamycin lotion 1 coupon</a> There is no danger of an eruption. "That's probably a good thing, since we've been able to correlate mass extinction with some of these beasts," said Clive R. Neal, a volcanologist at the University of Notre Dame.

发表评论

博客统计

访问量:2920041

博文总数:749 评论总数:312751

原创125 翻译20 转载604