CODEWITHSUNDEEP
oracle-databasetriggersdynamic-sql
galme
galme

Reputation: 631

How to store sysdate value in a variable in triggers in oracle?

I have a trigger written as below:

create or replace
TRIGGER impt_downloadproc
before delete ON A
declare
storedate nvarchar2(80);
storetime nvarchar2(80);
sequel string(2000);
BEGIN
storedate := to_char(sysdate,'YYYYMMDD');
storetime :=  to_char(sysdate,'HH24MISS');
sequel:='create table B_'||storedate||'_'||storetime||' as select * from ipcsdd_download_process';
execute immediate sequel;
END;

What I am trying to do in my trigger is : Before someone/something deletes record(s) from A table, create a backup table B_yyyymmdd_hhmmss and backup the records to this table.

But I get error in line :storedate := to_char(sysdate,'YYYYMMDD'); I dont understand what is the problem.

Upvotes: 1

Views: 1502

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16915

I would expect a different error- you cannot commit in a trigger. If you really want to then you'll need to use an autonomous transaction (which is mostly not a good idea)

So, your code should look something like this:

create or replace
TRIGGER impt_downloadproc
before delete ON A
declare
pragma   autonomous_transaction; -- see this line
storedate nvarchar2(80);
storetime nvarchar2(80);
sequel string(2000);
BEGIN
storedate := to_char(sysdate,'YYYYMMDD');
storetime :=  to_char(sysdate,'HH24MISS');
sequel:='create table B_'||storedate||'_'||storetime||' as select * from ipcsdd_download_process';
execute immediate sequel;
END;

here is an example

Upvotes: 2

Related Questions