CODEWITHSUNDEEP
sqloracle-databasedatabase-trigger
Sho
Sho

Reputation: 39

How to invoke/call a sequence inside a trigger

I want to auto-increment the table id I have created. This table will save logs about changes made from a certain table in my database. Below is my code and the error I'm getting. Please help me and thank you in advance. 

create or replace Trigger PVFC_audit
after insert or delete or update on customer
for each row 
declare
v_user varchar2(20);
eventlog_id_go number;
v_date date;
v_time timestamp;
act_done varchar2(20) := case when updating then 'Update' when deleting then 'Delete' else 'Insert' end;
begin
IF eventlog_id IS NULL THEN  <--- eventlog_id is the actual column in my eventlogs table
SELECT eventlog_id_seq.NEXTVAL , user, To_Char(sysdate,'DD/MON/YYY'), To_char(CURRENT_TIMESTAMP,'HH12:MI:SS')
INTO eventlog_id_go, v_user, v_date, v_time from dual;
END IF;
if inserting then
    insert into eventlogs(eventlog_id, user_name, date_done, time_done, action_done, object_name)
    values (eventlog_id_go, v_user, v_date, v_time, act_done , 'customer');
elsif deleting then
    insert into eventlogs(eventlog_id, user_name, date_done, time_done, action_done, object_name)
    values (eventlog_id_go, v_user, v_date, v_time, act_done,'customer');
elsif updating then
    insert into eventlogs(eventlog_id, user_name, date_done, time_done, action_done, object_name)
    values (eventlog_id_go, v_user, v_date, v_time, act_done ,'customer');
end if;
end;

-----Below is the sequence I've created-----

CREATE SEQUENCE  "SYSTEM"."EVENTLOG_ID_SEQ"  
MINVALUE 1 
MAXVALUE 9999999999999999999999999999 
INCREMENT BY 1 
START WITH 1 
CACHE 20 
NOORDER  
NOCYCLE ;

Error(13,4): PLS-00201: identifier 'EVENTLOG_ID' must be declared

Upvotes: 0

Views: 439

Answers (2)

APC
APC

Reputation: 146219

eventlog_id is a column from another table named eventlogs

So you can't reference it in a trigger: a trigger only sees the columns of the table which owns it. The solution is just populate the eventlog_id column in the INSERT statement.

Incidentally your trigger has a lot of duplication. You can simplify immensely: 

create or replace Trigger PVFC_audit
after insert or delete or update on customer
for each row 
declare
    act_done varchar2(20) := case when updating then 'Update' when deleting then 'Delete' else 'Insert' end;
begin

    if inserting then
        act_done := 'INSERT';
    elsif deleting then
        act_done := 'DELETE';
    elsif updating then
        act_done := 'UPDATE';
    end if;
    insert into eventlogs(eventlog_id, user_name, date_done, time_done, action_done, object_name)
    values (eventlog_id_seq.NEXTVAL , user, To_Char(sysdate,'DD/MON/YYY'), To_char(CURRENT_TIMESTAMP,'HH12:MI:SS')
    , act_done ,'customer');

end;

Not sure why you're populating date_done andtime_done as strings. It would make way more sense to use the correct data types. 

    insert into eventlogs(eventlog_id, user_name, date_done, time_done, action_done, object_name)
    values (eventlog_id_seq.NEXTVAL , user, trunc(sysdate), CURRENT_TIMESTAMP
    , act_done ,'customer');

If EVENTLOGS has those columns defined as VARCHAR2 then that is almost definitely a data model bug which will give you grief at some point.

Upvotes: 3

Pablo notPicasso
Pablo notPicasso

Reputation: 3161

Yo need to reference pseudorecords old/new value

IF :OLD.eventlog_id IS NULL THEN

or

IF :NEW.eventlog_id IS NULL THEN

Upvotes: 0

Related Questions