thanks thomas ,
for your quick reply
but i got some problem......

1>you have create it on sql ,Would i make it on pl/sql , i have tried it on pl/sql the error details are

2> when i run it in after, editing the code it says that , insufficient privillege
as

SQL> @ shut_trig
create or replace trigger shut_trig before shutdown on database
*
ERROR at line 1:
ORA-01031: insufficient privileges




so i compile it by using user as sys . it compiled with compilation error.
as

SQL> connect sys
Enter password: ******
Connected.
SQL> @ shut_trig

Warning: Trigger created with compilation errors.

SQL> set termout on
SQL> shutdown
ORA-01031: insufficient privileges


3>why we use @connect"/as sysdba" and set termout off stands for.

4> when i shutdown under sys user it says insuffiecent privillege.


so i could not make it in pl/sql

with warm regards

from samar

fisrt- could i make it on pl/sql?


second -if i make it i tried it and the result is it did not insert into the table .(named as shut_trig_det )

i am giving the total details i had tried so far.
------
i had tried it pl/sql by creating the below shut_trig trigger in editer.

create or replace trigger shut_trig before shutdown on database

begin
insert into shut_trig_det (user_id,log_date,action) values (user,sysdate,'shutting down');
end;
/

i have an table shut_trig_det as


SQL> desc SHUT_TRIG_DET;
Name Null? Type
------------ -------- -----------------
USER_ID VARCHAR2(15)
LOG_DATE DATE
ACTION VARCHAR2(15)


and then tried in sql as below

SQL> select * from SHUT_TRIG_DET; -- in user scott

no rows selected

SQL> connect /as sysdba
Connected.
SQL> @ shut_trig

Warning: Trigger created with compilation errors.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 128665628 bytes
Fixed Size 75804 bytes
Variable Size 57839616 bytes
Database Buffers 70672384 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SQL> select * from SHUT_TRIG_DET;
select * from SHUT_TRIG_DET
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect
Enter user-name: scott
Enter password: *****
Connected.

SQL> select * from SHUT_TRIG_DET; -- it was not inserted

no rows selected


that means it does not inserted pl/sql?

thanking you thomas
from
samar

The trigger has compilation error.Need to use scott.shut_trig_det instead of shut_trig_det.

But why we have to use autonomous transaction ? Can't that trigger work sucessfully without autonomous transaction.

Samar,

The trigger that you created in SYS needs to have access to the table that you are writing to - you seem to have created that table in SCOTT? Create it in SYS and recompile your trigger.

You need to use an autonomous transaction because the session firing the trigger will not commit - the database is shutting down.

Tom,

From your:

Followup:
I was demonstrating the concept was sound, I wanted the record to be committed in the trigger itself - regardless of anything else that happened. Hence I used an autonomous transaction.

I see that my thought that the autonomous transaction was required because the database was shutting down is incorrect. Is this trigger the only case in which a shutdown immediate/abort will commit this transaction when other transactions would be rolled back?

I wanted to create a database shutdown trigger to disable several triggers and run a procedure, before the shutdown.

I created the trigger as sys(Is this a must?? or can I create this as scott??)

--Login as sys
CREATE OR REPLACE TRIGGER scott_shutdown_integ_trig
BEFORE SHUTDOWN ON DATABASE

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR cGetScheduleIds IS
SELECT schedule_id
FROM scott.batch_schedule
WHERE schedule_name IN
('scott Process Err Mess', 'scott Get All Work Orders');

BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_service_run DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_work_order_note DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_customer_info DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_finished_wonote DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_service_site DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_tax_rate DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_service_site DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_cust_hier_upd DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_cust_hier_ins DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_cust_credit_upd DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_delete_service_run DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_delete_cust_hier_upd DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_customer_info DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_cust_credit_ins DISABLE';

FOR rec_ IN cGetScheduleIds LOOP
scott.Batch_Schedule_API.Deactivate__(rec_.schedule_id);
END LOOP;
COMMIT;
END;

I was able to create the trigger successfully. But it doesn't really disable the triggers or do anything inside the procedure
when I bounce the database back.

we are on Oracle 10gR2 and Windows server 2003, if that matters.

I appreciate your help to see what's wrong here.

THanks,

Kandy Train

Hi,

We have a requirement to truncate few tables before shutting down the database. I created trigger as under, but this is not working:

CREATE OR REPLACE TRIGGER TETS_PURGE BEFORE SHUTDOWN ON DATABASE
begin
execute immediate 'TRUNCATE TABLE TEST_TABLE';
END;
/

Please suggest