| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: CREATE TRIGGER history?????????
In the trigger, you can't select the table you are inserted in nor use the commit statement. You have to use :new record that contains the current inserted column values.
Something like:
CREATE OR REPLACE TRIGGER History
AFTER INSERT ON Merchant_Partnert
FOR EACH ROW
BEGIN
INSERT INTO Merchant_Partnert_Hist (row_id, network_id, ...)
values (:new.row_id, :new.network_id, ...);
END History;
/
-- Have a nice day Michel Arthur Levin <arthur_at_stario.com> a écrit dans le message : QLpN4.993$Pi2.27298_at_news.pacbell.net...Received on Wed Apr 26 2000 - 00:00:00 CDT
> Hello,
> I have two identical tables.
> Primary and History table.
> I insert data in Primary table and need insert the same data in History
> table
> PS. I need history table to keep 'Clear' not updated data.
> I created index but got ERROR
>
>
> SQL> CREATE OR REPLACE TRIGGER History
> AFTER INSERT ON Merchant_Partnert
> FOR EACH ROW
> BEGIN
> INSERT INTO Merchant_Partnert_Hist
> SELECT * FROM Merchant_Partnert where row_id= row_id_seq.CURRVAL ;
> commit;
> END History;
> /
>
> Trigger created.
>
> When I run transactions I receive ERROR.
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
>
>
> -- Primary table
> -- TABLE: Merchant_Partner
> --
>
> CREATE TABLE Merchant_Partner(
> row_id INTEGER NOT NULL,
> network_id INTEGER,
> merchant_id INTEGER,
> merchant_partner_id INTEGER,
> active CHAR(1) default 't' check (active in
> ('t', 'f')),
> creator VARCHAR2(50),
> creation_date DATE,
> CONSTRAINT ROW_ID_PK PRIMARY KEY (row_id),
> CONSTRAINT NETWORK_ID1_FK FOREIGN KEY (network_id) REFERENCES
> Merchant_Nwk(network_id),
> )
> ;
> create sequence row_id_seq increment by 100;
>
> -- History table
> -- TABLE: Merchant_Partner_Hist
> --
>
> CREATE TABLE Merchant_Partner_Hist(
> row_id INTEGER NOT NULL,
> network_id INTEGER,
> merchant_id INTEGER,
> merchant_partner_id INTEGER,
> active CHAR(1) default 't' check (active in
> ('t', 'f')),
> creator VARCHAR2(50),
> creation_date DATE,
> CONSTRAINT ROW_IDH_PK PRIMARY KEY (row_id),
> CONSTRAINT NETWORK_ID1H_FK FOREIGN KEY (network_id) REFERENCES
> Merchant_Nwk(network_id),
> )
> ;
>
> Any Ideas how to insert data in history table?
>
> Thank you,
> Arthur
>
>
>
 |
 |