Trigger Err.....
Hi all,
I am very new to oracle DB
I was trying to write a trigger but got error.
What I am trying to do is before insert of each row I would like to add the system date to a column.
I would ike to have my date in this format '12-MAR-04'
This is my trigger
CREATE OR REPLACE TRIGGER add_date
AFTER INSERT ON cbdetails
FOR EACH ROW
PRAGMA AUTONOMOUS TRANSACTION
DECLARE
Tdate CHAR(10);
BEGIN
Tdate := TO_CHAR(TO_DATE(sysdate,'DD-MON-RR'));
IF INSERTING THEN
INSERT INTO cbdetails (DC_CDATE) values (Tdate);
END IF;
COMMIT;
END;
I also tried adding sysdate to the default parameter when i created table and it worked.
but I need to make sure the sysdate is in right format. I heard that this format differs from system to system is that correct?
# 1 Re: Trigger Err.....
In your case two problem i have found due to which you facing:
1. You trying to access same table i.e. Triggering Table, for which you have created the trigger.
2. You trying to save varchar type variable value in Date type field.
To overcome i have following solution:
1. If you want to update any field of Triggering table instead of referring that table refer :New table which is available in insert or update trigger which is nothing but same trigger table but with just one row which you trying to insert and for updating value of any triggering table use following syntax:
:New.DC_CDATE := sysdate;
And for Format of date is concerned that only applicable to display of Date value and for storing any value in Date field of Oracle always pass value in 'DD-MON-YY' format.
2. In your case if you want to save system date in date field column then use syntax given above.
ITGURU at 2007-11-9 13:37:22 >
