Trigger

[michell o'brien]

I have written a trigger not sure where I am going wrong. It is showing as Invalid trigger.

 CREATE OR REPLACE TRIGGER NASEEM.INSERT_ITEM
AFTER INSERT ON NASEEM.ITEMTYPE 
DECLARE ITEMTYPE_VALUES CURSOR FOR
BEGIN
SELECT ITEM_TYPE_NO, ITEM_TYPE_NAME 
FROM ITEMTYPE
WHERE ITEM_TYPE_NO < SEQ_ITEMTYPE_ID.NEXTVAL - 1
END;   

[John Bengler]

Hi Michell,

what exactly do you want this trigger to do?


John

[michell o'brien]

This is my first trigger. I am selecting some of the newly inserted values.using a trigger.

[John Bengler]

Ok, I see...

Wel, it looks as if you need some guidance on triggers.

Maybe you can start following one of these links:

Tutorial Oracle Triggers

When to use triggers


And one more thing: inside of a trigger you should avoid selects on the table of the trigger. See this:

Avoiding Mutating Tables


John

[michell o'brien]

  CREATE OR REPLACE TRIGGER INSERT_ITEM
AFTER INSERT ON ITEMTYPE
DECLARE 
CURSOR C_ITEMTYPE_VALUES IS 
SELECT ITEM_TYPE_NO, ITEM_TYPE_NAME 
FROM ITEMTYPE;
BEGIN
 DELETE FROM ITEM;
 FOR V_ITEMTYPE_VALUES IN C_ITEMTYPE_VALUES LOOP
 INSERT INTO ITEM(ITEM_ID, ITEM_TYPE_NO, ITEM_NAME, AVAILABILITY_FLAG)
 VALUES(SEQ_ITEMTYPE_ID.NEXTVAL, C_ITEMTYPE_VALUES.ITEM_TYPE_NO, C_ITEMTYPE_VALUES.ITEM_NAME, 1);
END LOOP;
END INSERT_ITEM;  

Can anyone tell me what mistake I have done in Line 10. It gives me error code number PLSQL-00103

[John Bengler]

Hi michell,

I think when you work with cursors you have to open them, e.g.

   
create or replace procedure p_printEmps is
   begin -- for main function
       declare
           cursor c_emp is
             select * from employee;
             r_emp c_emp%ROWTYPE;
       begin
           open c_emp;
           loop
               fetch c_emp into r_emp;
               exit when c_emp%NOTFOUND;
               DBMS_OUTPUT.put_line(r_emp.first_Name);
           end loop;
           close c_emp;
       end;
   end;
   /

Additionally I'm not sure if you're allowed to access table itemtype in your trigger,but since it is an AFTER trigger it may be allowed...

But I think you should read about the :new and : old parameters available in triggers.

If you just want to add an item everytime a new itemtype was added you could write:

 CREATE OR REPLACE TRIGGER INSERT_ITEM  
 AFTER INSERT ON ITEMTYPE  
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
 BEGIN
  INSERT INTO ITEM(ITEM_ID, ITEM_TYPE_NO, ITEM_NAME, AVAILABILITY_FLAG)  
  VALUES(SEQ_ITEMTYPE_ID.NEXTVAL, :NEW.ITEM_TYPE_NO, :NEW.ITEM_NAME, 1);  
 END INSERT_ITEM;       

So for each row which was inserted into itemtypes an entry into item is made.
If you create this trigger before the firstentry into itemtype was made it will have the same effect as your trigger - of course without "cleaning up" items whose itemtype has been deleted. For this you would need an additional ON DELETE trigger..


John