Hi All,
Your Input expertise is very much appreciated.
Have an issue where this is my Master table structure, whenever there is a update/delete/insert on the master table
there is a trigger that insert a entry into Audit table.
Sql > Desc cpp_Master;
Ent_id Number,
Ent_date date
Sql > Desc Cpp_Master_Audit
Ent_id Number,
Ent_date date,
Insert_type varchar2(1)
The problem is that when we insert the below record into table the ent_date column appears this way.
Sql > INSERT INTO CED.cpp_Master (ENT_ID, Ent_date)
VALUES (6030902, TO_DATE ('01/01/0001 00:00:00', 'MM/DD/YYYY HH24:MIS'));
Sql > SELECT * FROM CED.cpp_Master;
6030902 01/01/0001
Sql > SELECT * FROM CED.Cpp_Master_Audit;
6030902 01/01/0001
But when i update the date is coming up as below
Sql > update ced.cpp_Master set Ent_date = to_date('0001-MAR-01', 'YYYY-mon-dd') where ent_id='6030902';
Sql > Commit;
Tried below updates as well
(
update ced.cpp_Master set Ent_date = '0001-MAR-01' where ent_id='6030902';
update ced.cpp_Master set Ent_date = to_char('0001-MAR-01', 'YYYY-mon-dd') where ent_id='6030902';
)
Every time the output as below always. (Insert has no problem only update has problem)
I wonder why when there is a insert the value of date comes up as "01/01/0001" in (Cpp_Master_Audit), but when i update the value of date comes up "01/01/2001"
I need the value to be inserted as "01/01/0001", as whatever been updated in Master table(Cpp_Master)
Sql > SELECT * FROM CED.cpp_Master;
6030902 01/01/2001
My trigger query:
CREATE OR REPLACE TRIGGER CED.CPP_AUDT
AFTER INSERT OR UPDATE OR DELETE
ON CED.cpp_Master REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
l_action_code VARCHAR2 (1);
BEGIN
IF INSERTING
THEN
l_action_code := 'I';
ELSIF UPDATING
THEN
l_action_code := 'U';
ELSE
l_action_code := 'D';
END IF;
IF ( l_action_code = 'I'
OR l_action_code = 'D'
OR ( l_action_code = 'U'
AND ( NVL (:OLD.ENT_ID, 0) <> NVL (:NEW.ENT_ID, 0)
OR NVL (TO_CHAR (:OLD.Ent_date, 'mm/dd/yyyy'),
'X') <>
NVL (TO_CHAR (:NEW.Ent_date, 'mm/dd/yyyy'),
'X')
)))
THEN
INSERT INTO CED.Cpp_Master_Audit (
ENT_ID,Ent_date,Insert_type
)
VALUES (DECODE (:OLD.ent_id, NULL, :NEW.ent_id, :OLD.ent_id),
DECODE (l_action_code,'I',:NEW.Ent_date,'D',NULL,'U',DECODE (:NEW.Ent_date,:OLD.Ent_date, NULL,:NEW.Ent_date)),
Insert_type l_action_code);
END IF;
END;
/