Re: Trigger "AFTER LOGON ON SCHEMA" never works.

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Sun, 02 Nov 2003 07:24:37 GMT
Message-Id: <pan.2003.11.02.07.24.37.43142@adelphia.net>

On Tue, 28 Oct 2003 09:00:07 +0100, Lav Kovacic wrote:

> I have created a trigger with the following script:
>
> CREATE OR REPLACE TRIGGER "LAV"."PROBA"
> AFTER LOGON ON SCHEMA
> BEGIN
>
> INSERT INTO aa_log values (
> 10000,
> 'Trigger',
> 1,
> 'AAA',
> 'BBB');
>
> COMMIT;
>
> END;
>
> User that creates the trigger has "CREATE TRIGGER" and
> "ADMINISTER DATABASE TRIGGER" privileges.
>
> The trigger is successfully compiled and enabled.
>
> The Insert command (from triggers body) was tested separately, and it works.
>
> However, when the user performs logon, nothing is inserted into
> the table, so I conclude that the trigger is not fired.
> What should I do to make it work?
>
> Server: Solaris 9, Oracle 9i
> Client: Win2k, SP4
>
> Thanks in advance,
> Lav

!) Your syntax is incorrect. Here is the proper one:

   SQL> create or replace trigger usenet   2 after logon on scott.schema
  3 begin
  4 insert into a values(user);
  5 end;
  6 /  

Trigger created.    

SQL> select * from a;  

USR

---

SCOTT   SQL> /   USR

---

SCOTT
SCOTT   SQL> That means that the proper syntax would be:   "create or replace trigger proba after logon on lav.schema"

Other then that, you cannot commit in a trigger. Lose that commit, fix the syntax and things will be hunky dory.

-- 
None of us is as dumb as all of us.
(http://www.despair.com/meetings.html)

Received on Sun Nov 02 2003 - 01:24:37 CST