Re: Help on Insert Triggers

johnparcels_at_gmail.com wrote:
> Thank you for help.
> 10.1.0.4.0
>
> Create or replace trigger mytrigger
> before drop on TableA
> for each row
> begin
> Insert into TableB
> values(:old.ColID,:old.Col1,:old.Col2,:old.Col3,:old.Col4,:old.Col5,:old.Col6);
> exception
> when DUP_VAL_ON_INDEX then
> null;
> end;
> /
>
>
> DA Morgan wrote:
>

>>johnparcels_at_gmail.com wrote:
>>
>>>When I tried to create this trigger, I am getting this error message?
>>>
>>>ORA-30506: system triggers cannot be based on tables or views
>>>Cause: An attempt was made to base a system trigger on a table or a
>>>view.
>>>Action: Make sure the type of the trigger is compatible with the base
>>>object.
>>>
>>>Any Ideas?
>>
>>Two to be precise.
>>
>>1. Post your Oracle version
>>2. Post your code
>>--
>>Daniel A. Morgan
>>http://www.psoug.org
>>damorgan_at_x.washington.edu
>>(replace x with u to respond)

1. Please do not top post.
2. In your follow-up you wrote: "did I really write "before drop" ??? Man, it's "before delete"

Actually sir it is not. The syntax is BEFORE DROP if it is a DDL trigger and BEFORE DELETE if it is a table trigger. But your 'Subject', above indicates you want help with an INSERT trigger so you leave this reader hopelessly confused as to what it is you are actually trying to do.

But just for sake of a wild guess lets assume you really do want a BEFORE DELETE trigger.

CREATE TABLE b (
col1 VARCHAR2(20));

INSERT INTO b VALUES ('ABC');
INSERT INTO b VALUES ('DEF');
INSERT INTO b VALUES ('123');

CREATE TABLE a (
col2 VARCHAR2(20));

CREATE OR REPLACE TRIGGER mytrigger
BEFORE DELETE ON b
FOR EACH ROW BEGIN
   INSERT INTO a
   (col2)
   VALUES
   (:old.col1);
END mytrigger;
/

DELETE FROM b WHERE rownum = 1;

SELECT * FROM a;
SELECT * FROM b;

What's the problem?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)