Home » SQL & PL/SQL » SQL & PL/SQL » Database System Triggers - Urgent
Database System Triggers - Urgent [message #36455] Wed, 28 November 2001 22:04 Go to next message
Sridhar J
Messages: 5
Registered: November 2001
Junior Member
kindly let me know how to write a System trigger to delete a table every 3 rd day ..

reply asap

----------------------------------------------------------------------
Re: Database System Triggers - Urgent [message #36465 is a reply to message #36455] Thu, 29 November 2001 02:55 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
you use a table named tbl_days where you save a value for counting days, when the value is 3 you delete table you want, if not you increase value in tbl_days.

CREATE OR REPLACE TRIGGER three_day
BEFORE logoff ON DATABASE
DECLARE
val NUMBER;
BEGIN
SELECT val INTO value FROM tbl_Days WHERE p = 'value';
IF value = 3 THEN
EXECUTE IMMEDIATE 'drop table <your table name>';
UPDATE tbl_Days SET val = 1 WHERE p = 'value';
ELSE
UPDATE tbl_Days SET val = val + 1 WHERE p = 'value';
END;

----------------------------------------------------------------------
Re: Database System Triggers - Urgent [message #36469 is a reply to message #36455] Thu, 29 November 2001 03:58 Go to previous messageGo to next message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Of course, this doesn't count days, it counts logoffs. which is great if you have 1 user / connecting system that logs in at the start of the day and ONLY logs off at the end of the day.

Instead of storing a count it would be much more appropriate to store TRUNC(SYSDATE) on the drop, and then check TRUNC(SYSDATE) > val + 3.

Or even... as is the case with most systems that need to deal with overnight processes... you use the organisation / system stored date (incremented by your overnight process) from your system variables table to do the comparisons instead of TRUNC(SYSDATE)

That avoids your data being deleted a day early by that pesky hard worker that logs off just past midnight...

----------------------------------------------------------------------
Re: Database System Triggers - Urgent [message #36490 is a reply to message #36455] Thu, 29 November 2001 19:16 Go to previous messageGo to next message
Sridhar J
Messages: 5
Registered: November 2001
Junior Member
Thanks a lot,
but i don,t want to maintain the value of 3 days in the table, but based on the Sysdate it has to delete automatically

----------------------------------------------------------------------
Re: Database System Triggers - Urgent [message #36492 is a reply to message #36469] Thu, 29 November 2001 19:45 Go to previous message
Sridhar J
Messages: 5
Registered: November 2001
Junior Member
Thanks for the info,
Basically the trigger should be based on the SYSDATE.
But the problem is, when the SYSDATE is getting changed in the SYSTEM,
the triggers should be excecuted,
i.e if the local value (DATE value ) inside the trigger is the 3rd day wrt the SYSDATE then the trigger has to be executed.

----------------------------------------------------------------------
Previous Topic: dbms_output.put_line buffer limit
Next Topic: Error messages
Goto Forum:
  


Current Time: Thu Mar 28 11:59:38 CDT 2024