oracle 使用触发器插入/更新到另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23882616/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-19 02:22:04 来源:igfitidea点击:
Insert/Update to another table using trigger
提问by king jia
I have 2 tables, tableA and tableB.
我有 2 张桌子，tableA 和 tableB。
tableA:
id | status | Flag | ResultOfAction | ExternalId | Customer
1 200 2 0 332 C1
2 200 0 0 333 C1
If old.status <> new.status OR Flag = 3 OR ResultofAction = 1, the trigger will populate another table.
如果 old.status <> new.status OR Flag = 3 OR ResultofAction = 1，触发器将填充另一个表。
e.g
例如
tableB:
id | status | Flag | ResultOfAction | ExternalId | tableAId
1 200 2 0 332 1
if tableA has new insertion:
如果 tableA 有新的插入：
tableA:
id | status | Flag | ResultOfAction | ExternalId | Customer
1 200 2 0 332 C1
2 200 0 0 333 C1
3 200 2 0 334 C1
TableB will populate new row automatically.
TableB 将自动填充新行。
tableB:
id | status | Flag | ResultOfAction | ExternalId | tableAId
1 200 2 0 332 1
2 200 2 0 334 3
If one of the row in tableA update. e.g. id of 1 to 300, then tableB
如果 tableA 中的行之一更新。例如 1 到 300 的 id，然后是 tableB
tableB:
id | status | Flag | ResultOfAction | ExternalId | tableAId
1 300 2 0 332 1
2 200 2 0 334 3
Here is my exising Trigger. Anyone can help?
这是我现有的触发器。任何人都可以帮忙吗？
CREATE OR REPLACE TRIGGER TABLEA_TRG AFTER INSERT OR UPDATE ON TABLEA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (OLD.STATUS <> NEW.STATUS OR NEW.FLAG = 3 or NEW.RESULTOFACTION = 1)
BEGIN
IF INSERTING THEN
INSERT INTO TABLEB(TABLEAID, ExternalId, STATUS, Flag)
SELECT :NEW.ID, :NEW.ExternalId, :NEW.STATUS, :NEW.FLAG FROM DUAL
WHEN NOT EXISTS (SELECT 1 FROM TABLEB WHERE tableAId = :NEW.id);
ELSIF UPDATING THEN
IF :NEW.STATUS <> :OLD.STATUS THEN
UPDATE TABLEB DWT SET DWT.tableAId = :NEW.id, DWT.ExternalId = :NEW.ExternalId,
DWT.STATUS = :NEW.STATUS, DWT.Flag = :NEW.Flag;
END IF;
MERGE INTO TABLEB D
USING DUAL ON (D.TABLEAID = :NEW.ID)
WHEN MATCHED THEN
UPDATE SET D.STATUS = :NEW.STATUS
WHEN NOT MATCHED THEN
INSERT (D.TABLEAID, D.ExternalId, D.STATUS, D.FLAG) VALUE (:NEW.ID, :NEW.ExternalId, :NEW.STATUS, :NEW.FLAG);
END IF;
END;
/
There are errors * Error(7,7): PL/SQL: SQL Statement ignored * Error(9,16): PL/SQL: ORA-00933: SQL command not properly ended
有错误 * Error(7,7): PL/SQL: SQL 语句被忽略 * Error(9,16): PL/SQL: ORA-00933: SQL 命令没有正确结束
This is my first time doing Trigger in Oracle, can someone help me please?
这是我第一次在 Oracle 中使用 Trigger，有人可以帮我吗？
回答by PT_STAR
check this out:
看一下这个：
CREATE OR REPLACE TRIGGER TABLEA_TRG AFTER INSERT OR UPDATE ON TABLEA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (OLD.STATUS <> NEW.STATUS OR NEW.FLAG = 3 or NEW.RESULTOFACTION
begin
IF INSERTING THEN
begin
INSERT INTO TABLEB
(TABLEAID, ExternalId, STATUS, Flag) values
(:new.id, :new.externalId, :new.status, :new.flag);
-- assuming, there is an unique key on id
exception
when dup_val_on_index then
null;
end;
ELSIF UPDATING THEN
IF :NEW.STATUS <> :OLD.STATUS THEN
UPDATE TABLEB DWT
SET DWT.tableAId = :NEW.id,
DWT.ExternalId = :NEW.ExternalId,
DWT.STATUS = :NEW.STATUS,
DWT.Flag = :NEW.Flag;
END IF;
MERGE INTO TABLEB D
USING DUAL
ON (D.TABLEAID = :NEW.ID)
WHEN MATCHED THEN
UPDATE SET D.STATUS = :NEW.STATUS
WHEN NOT MATCHED THEN
INSERT
(D.TABLEAID, D.ExternalId, D.STATUS, D.FLAG) VALUEs
(:NEW.ID, :NEW.ExternalId, :NEW.STATUS, :NEW.FLAG);
END IF;
end test;
回答by BRAHMA PRAKASH SHUKLA
USE [SocRamala]
GO
/****** Object: Trigger [dbo].[tgr_LOGMILLPUR] Script Date: 8/1/2018 1:56:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tgr_LOGMILLPUR] on [dbo].[MILL_PUR]
after UPDATE, INSERT, DELETE
as
if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
INSERT into LOG_MILLPUR(M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO,M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date, LOG_STATUS, LOG_DATE,LOG_ACTIVITY, LOG_IP, LOG_SQLUSER, LOG_PCNAME)
Select M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date, 'NEW', GETDATE(), 'UPDATE',CONVERT(NVARCHAR,ISNULL(CONNECTIONPROPERTY('client_net_address'),'')),CONVERT(NVARCHAR,ISNULL(SUSER_SNAME(),'')),CONVERT(NVARCHAR,ISNULL(HOST_NAME(),'')) from inserted i ;
INSERT into LOG_MILLPUR(M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date, LOG_STATUS, LOG_DATE,LOG_ACTIVITY, LOG_IP, LOG_SQLUSER, LOG_PCNAME)
Select M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date, 'OLD', GETDATE(), 'UPDATE',CONVERT(NVARCHAR,ISNULL(CONNECTIONPROPERTY('client_net_address'),'')),CONVERT(NVARCHAR,ISNULL(SUSER_SNAME(),'')),CONVERT(NVARCHAR,ISNULL(HOST_NAME(),'')) from deleted D ;
end
If exists (Select * from inserted) and not exists(Select * from deleted)
begin
INSERT into LOG_MILLPUR(M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date, LOG_STATUS, LOG_DATE, LOG_ACTIVITY, LOG_IP, LOG_SQLUSER, LOG_PCNAME)
Select M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date, 'NEW', GETDATE(), 'INSERT',CONVERT(NVARCHAR,ISNULL(CONNECTIONPROPERTY('client_net_address'),'')),CONVERT(NVARCHAR,ISNULL(SUSER_SNAME(),'')),CONVERT(NVARCHAR,ISNULL(HOST_NAME(),'')) from inserted i ;
end
If exists(select * from deleted) and not exists(Select * from inserted)
begin
INSERT into LOG_MILLPUR(M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date, LOG_STATUS, LOG_DATE, LOG_ACTIVITY, LOG_IP, LOG_SQLUSER, LOG_PCNAME)
Select M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date, 'OLD', GETDATE(), 'DELETE',CONVERT(NVARCHAR,ISNULL(CONNECTIONPROPERTY('client_net_address'),''))LOG_IP,CONVERT(NVARCHAR,ISNULL(SUSER_SNAME(),''))LOG_SQLUSER,CONVERT(NVARCHAR,ISNULL(HOST_NAME(),''))LOG_PCNAME from deleted D ;
end
write your query in this way by only changing the columns name of the insert and select command of this code
通过仅更改此代码的插入和选择命令的列名称，以这种方式编写查询
回答by PT_STAR
I don't really got the problem but here is an example:
我没有真正遇到问题，但这里有一个例子：
I've got a TABLE_A and an auditing table (TABLE_A_AUDIT) which contains all operations on TABLE_A. Therefor I have three triggers, one for inserting, one for updating and one for delete.
我有一个 TABLE_A 和一个审计表 (TABLE_A_AUDIT)，其中包含对 TABLE_A 的所有操作。因此我有三个触发器，一个用于插入，一个用于更新，一个用于删除。
create table TABLE_A
(
key_value VARCHAR2(20),
value VARCHAR2(20)
)
create table TABLE_A_AUDIT
(
audit_timestamp TIMESTAMP(6),
audit_action VARCHAR2(1),
key_value VARCHAR2(20),
value VARCHAR2(20)
)
Insert Trigger:
插入触发器：
create or replace trigger table_a_insert before insert on table_a for each row
begin
insert into table_a_audit
(audit_timestamp, audit_action, key_value, value)
values
(systimestamp, 'I', :new.key_value, :new.value);
end;
Update Trigger:
更新触发器：
create or replace trigger table_a_update before update on table_a for each row
begin
insert into table_a_audit
(audit_timestamp, audit_action, key_value, value)
values
(systimestamp, 'U', :new.key_value, :new.value);
end;
Delete Trigger:
删除触发器：
create or replace trigger table_a_delete before delete on table_a for each row
begin
insert into table_a_audit
(audit_timestamp, audit_action, key_value, value)
values
(systimestamp, 'D', :old.key_value, :old.value);
end;
I hope this example helps.
我希望这个例子有帮助。
回答by PT_STAR
you cannot select :new.id, etc. from the DUAL table. This table only have the column "DUMMY". I'm not sure what you want, but I think you should use a sequence to get a new ID ...
您不能从 DUAL 表中选择 :new.id 等。该表只有“DUMMY”列。我不确定你想要什么，但我认为你应该使用一个序列来获得一个新的 ID......
