using variables in a trigger 1

[FALCONSEYE]

I am working on an update/delete trigger on tblProjects. The idea is to keep track of the project's version by keeping a history in tblProjectHistory.

I am not quite sure how to do this but this is what I have so far:

CREATE OR REPLACE TRIGGER trg_ProjectHistory
BEFORE UPDATE OR DELETE
ON tblPROJECTS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
declare tmpVersion number;
BEGIN
  select version+1 into tmpVersion from tblPROJECTS
                          where project_id = :old.project_id);

...

Every insert into history table will have previous version + 1. The above code throws an error. It doesn't like

select version+1 into tmpVersion from tblPROJECTS
                          where project_id = :old.project_id);

I found some info where they were calling functions. Should I write a function or is there a better way of doing this? Thanks in advance.

http://www.fuzzysiberians.com

 

[Dagon]

It depends exactly what you are trying to do, which isn't clear from your description. However, you can't select from tblProjects because the table is mutating (do a search for "mutating tables" to get more information on the problem). You can however do something like:

:new.version := ld.version + 1;

For Oracle-related work, contact me through Linked-In.

 

[FALCONSEYE]

thanks Dagon.
Sorry for misleading, the version field is actually in the history table. The project table does not have a field called version. I guess if i had that it would be easier. I created a function in my package. This seem to work: (function body)

 FUNCTION getProjectVersion ( projectID number) 
      return number
      is
        v_version number;
        v_projectID number := projectID;
     begin 
        select max(version) + 1 into v_version
        from tblProject_history
        where project_id = v_projectID;
        return v_version;
     end;

in my trigger:

 select ICS.GETPROJECTVERSION( :OLD.project_ID ) into   tmpVersion from dual;

not sure if this is the most efficient way of doing.
thanks

http://www.fuzzysiberians.com

 

[Dagon]

I'd question why you need to select it from dual. You can just do:

tmp_version := ICS.GETPROJECTVERSION( :OLD.project_ID );

Also, might be worth changing max(version) to NVL(max(version,1)) to cope with the situation where there is no history row.


For Oracle-related work, contact me through Linked-In.

 

[Dagon]

Should be NVL(max(version),1)

For Oracle-related work, contact me through Linked-In.

 

[FALCONSEYE]

Thanks Dagon. Yes, I had to modify the function to include nvl. Otherwise, it threw an error saying "cannot insert null error." when there is no history.

http://www.fuzzysiberians.com

 

[FALCONSEYE]

my function handles nvl:

FUNCTION getProjectVersion ( projectID number) 
      return number
      is
        v_version number;
        v_projectID number := projectID;
     begin 
        select nvl(max(version),0) + 1 into v_version
        from project_history
        where project_id = v_projectID;
        return v_version;
     end;

http://www.fuzzysiberians.com