Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to execute procedure
Trigger to execute procedure [message #239086] Mon, 21 May 2007 08:01 Go to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Hello, I have a procedure named "proc_1", and I want it to be executed every time "user3" is logging off.

So, everytime "user3" ends his/her session, I want a trigger to tell the procedure to execute... is this possible?

Re: Trigger to execute procedure [message #239088 is a reply to message #239086] Mon, 21 May 2007 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you call it in a logoff trigger.

Regards
Michel
Re: Trigger to execute procedure [message #239094 is a reply to message #239088] Mon, 21 May 2007 08:21 Go to previous messageGo to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
What a quick response, this forum is truly amazing.

Okej, so this is how far I have managed;

CREATE OR REPLACE TRIGGER trigg_4 after logoff on database
begin
EXECUTE proc_1
end;

How do I make this trigger (if it is wright written) to only trigger when the specific user "user3" loggs of.
Re: Trigger to execute procedure [message #239101 is a reply to message #239094] Mon, 21 May 2007 08:52 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
pgorama wrote on Mon, 21 May 2007 08:21
What a quick response, this forum is truly amazing.

Okej, so this is how far I have managed;

CREATE OR REPLACE TRIGGER trigg_4 after logoff on database
begin
EXECUTE proc_1
end;

How do I make this trigger (if it is wright written) to only trigger when the specific user "user3" loggs of.



You can't use execute inside a sql block.

CREATE OR REPLACE TRIGGER trigg_4 after logoff on database
declare
  x_user varchar2(31);
begin
select user 
into x_user
from dual;

if user = 'USER3' then
  proc_1;
end if;
end;

[Updated on: Mon, 21 May 2007 08:52]

Report message to a moderator

Re: Trigger to execute procedure [message #239119 is a reply to message #239101] Mon, 21 May 2007 09:15 Go to previous messageGo to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Bill B wrote on Mon, 21 May 2007 15:52

CREATE OR REPLACE TRIGGER trigg_4 after logoff on database
declare
  x_user varchar2(31);
begin
select user 
into x_user
from dual;

if user = 'USER3' then
  proc_1;
end if;
end;



Thanks, but if I "select user into x_user from dual" wouldent I get the user currently logged on. I mean, if the trigger "triggs" when user "user3" loggs off, are you sure that the "select user from dual" will result in "user3".

And, must the trigger be created in "user3" schema? Becouse the "select user from dual" onley returns your own username?

Thank you again!
Re: Trigger to execute procedure [message #239154 is a reply to message #239119] Mon, 21 May 2007 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MICHEL> show user
USER is "MICHEL"
MICHEL> create table t (info varchar2(200), dt date default sysdate);

Table created.

MICHEL> create or replace trigger trg_bl before logoff on database
  2  begin
  3    insert into t (info) values (user);
  4    commit;
  5  end;
  6  /

Trigger created.

SCOTT> show user
USER is "SCOTT"
SCOTT> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

MICHEL> select * from t;
INFO                 DT
-------------------- -------------------
SCOTT                21/05/2007 17:32:58

1 row selected.

Does this answer your question?

Regards
Michel
Re: Trigger to execute procedure [message #239316 is a reply to message #239101] Tue, 22 May 2007 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Bill B wrote on Mon, 21 May 2007 15:52

CREATE OR REPLACE TRIGGER trigg_4 after logoff on database
declare
  x_user varchar2(31);
begin
select user 
into x_user
from dual;

if user = 'USER3' then
  proc_1;
end if;
end;



Why the select? You never even use x_user!
Re: Trigger to execute procedure [message #239379 is a reply to message #239086] Tue, 22 May 2007 03:56 Go to previous messageGo to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Yes it does, my problem was that I was thinking "after logoff on database" insted of "before". Thanks for the great example!

I've tryed the trigger and it works just fine.(With and without the select, using x_user='user3').
I had a lot of problem with "PLS-00306: wrong number or types of arguments in call to" in the beginning but thats becouse you cant use a procedure with "different for everytime" IN parameters in the trigger, right?

Thanks again!

Re: Trigger to execute procedure [message #239426 is a reply to message #239086] Tue, 22 May 2007 06:19 Go to previous messageGo to next message
breddypatil
Messages: 4
Registered: May 2007
Location: Bangalore
Junior Member
hi,
can anybody tell me exactly which trigger is used when the database is logedoff.i mean the exact name like "logoff on database".
Re: Trigger to execute procedure [message #239443 is a reply to message #239316] Tue, 22 May 2007 07:37 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Frank wrote on Tue, 22 May 2007 00:51
Bill B wrote on Mon, 21 May 2007 15:52

CREATE OR REPLACE TRIGGER trigg_4 after logoff on database
declare
  x_user varchar2(31);
begin
select user 
into x_user
from dual;

if user = 'USER3' then
  proc_1;
end if;
end;



Why the select? You never even use x_user!

It was a typo, I mean't to have "x_user = 'USER3'"
Re: Trigger to execute procedure [message #239446 is a reply to message #239426] Tue, 22 May 2007 07:40 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
breddypatil wrote on Tue, 22 May 2007 06:19
hi,
can anybody tell me exactly which trigger is used when the database is logedoff.i mean the exact name like "logoff on database".



The BEFORE SHUTDOWN ON DATABASE trigger
Re: Trigger to execute procedure [message #239735 is a reply to message #239446] Wed, 23 May 2007 03:57 Go to previous message
breddypatil
Messages: 4
Registered: May 2007
Location: Bangalore
Junior Member
thank you bill
Previous Topic: Useing dbms_jobs in backend
Next Topic: How to checking duplicated rows and copy it !!!
Goto Forum:
  


Current Time: Fri Dec 02 22:51:25 CST 2016

Total time taken to generate the page: 0.15370 seconds