DDL_TRIGGER not fire on DDL operator COMMENT ON, GRANT and REVOKE [CORE4358]

[firebird-automations]

Submitted by: @sim1984

Votes: 1

DDL_TRIGGER not fire on DDL operator COMMENT ON

CREATE TABLE DDL_LOG (
ID INTEGER NOT NULL,
MOMENT DATE NOT NULL,
USER_NAME VARCHAR(31) NOT NULL,
EVENT_TYPE VARCHAR(25) NOT NULL,
OBJECT_TYPE VARCHAR(25) NOT NULL,
DDL_EVENT VARCHAR(25) NOT NULL,
OBJECT_NAME VARCHAR(31) NOT NULL,
SQL_TEXT BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
);

ALTER TABLE DDL_LOG ADD PRIMARY KEY (ID);

CREATE SEQUENCE DDL_SEQ;

SET TERM ^ ;

CREATE OR ALTER TRIGGER TRIG_DDL_LOG_AFTER
ACTIVE AFTER ANY DDL STATEMENT POSITION 0
AS
BEGIN
INSERT INTO DDL_LOG (ID,
MOMENT,
USER_NAME,
EVENT_TYPE,
OBJECT_TYPE,
DDL_EVENT,
OBJECT_NAME,
SQL_TEXT)
VALUES (NEXT VALUE FOR DDL_SEQ,
CURRENT_TIMESTAMP,
CURRENT_USER,
RDB$GET_CONTEXT('DDL_TRIGGER', 'EVENT_TYPE'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_TYPE'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'DDL_EVENT'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_NAME'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'SQL_TEXT'));
END^

SET TERM ; ^

---------------------------------

set term ^;

create procedure p1
as
begin
end^

set term ;^

comment on procedure p1 is 'my proc';

drop procedure p1;

ID MOMENT USER_NAME EVENT_TYPE OBJECT_TYPE DDL_EVENT OBJECT_NAME SQL_TEXT
1 04.03.2014 22:00:42 SYSDBA CREATE PROCEDURE CREATE PROCEDURE P1
2 04.03.2014 22:00:42 SYSDBA DROP PROCEDURE DROP PROCEDURE P1

[firebird-automations]

Modified by: @sim1984

issuetype: Improvement [ 4 ] => Bug [ 1 ]

[firebird-automations]

Commented by: @asfernandes

By design, COMMENT ON does not trigger DDL triggers.

I Think ANY DDL STATEMENT means any possible trigger type used in DDL triggers, and COMMENT is not there.

[firebird-automations]

Commented by: @sim1984

ALTER DATABASE SET LINGER TO 60;
ALTER DATABASE DROP LINGER;
too it isn't added in the table DDL_LOG.

In addition to COMMENT ON operators of GRANT and REVOKE also fell out of reviewing

[firebird-automations]

Modified by: @sim1984

description: DDL_TRIGGER not fire on DDL operator COMMENT ON

CREATE TABLE DDL_LOG (
ID INTEGER NOT NULL,
MOMENT DATE NOT NULL,
USER_NAME VARCHAR(31) NOT NULL,
EVENT_TYPE VARCHAR(25) NOT NULL,
OBJECT_TYPE VARCHAR(25) NOT NULL,
DDL_EVENT VARCHAR(25) NOT NULL,
OBJECT_NAME VARCHAR(31) NOT NULL,
SQL_TEXT BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
);

ALTER TABLE DDL_LOG ADD PRIMARY KEY (ID);

SET TERM ^ ;

CREATE OR ALTER TRIGGER TRIG_DDL_LOG_AFTER
ACTIVE AFTER ANY DDL STATEMENT POSITION 0
AS
BEGIN
INSERT INTO DDL_LOG (ID,
MOMENT,
USER_NAME,
EVENT_TYPE,
OBJECT_TYPE,
DDL_EVENT,
OBJECT_NAME,
SQL_TEXT)
VALUES (NEXT VALUE FOR DDL_SEQ,
CURRENT_TIMESTAMP,
CURRENT_USER,
RDB$GET_CONTEXT('DDL_TRIGGER', 'EVENT_TYPE'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_TYPE'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'DDL_EVENT'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_NAME'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'SQL_TEXT'));
END^

SET TERM ; ^

---------------------------------

set term ^;

create procedure p1
as
begin
end^

set term ;^

comment on procedure p1 is 'my proc';

drop procedure p1;

ID MOMENT USER_NAME EVENT_TYPE OBJECT_TYPE DDL_EVENT OBJECT_NAME SQL_TEXT
1 04.03.2014 22:00:42 SYSDBA CREATE PROCEDURE CREATE PROCEDURE P1
2 04.03.2014 22:00:42 SYSDBA DROP PROCEDURE DROP PROCEDURE P1

=>

DDL_TRIGGER not fire on DDL operator COMMENT ON

CREATE TABLE DDL_LOG (
ID INTEGER NOT NULL,
MOMENT DATE NOT NULL,
USER_NAME VARCHAR(31) NOT NULL,
EVENT_TYPE VARCHAR(25) NOT NULL,
OBJECT_TYPE VARCHAR(25) NOT NULL,
DDL_EVENT VARCHAR(25) NOT NULL,
OBJECT_NAME VARCHAR(31) NOT NULL,
SQL_TEXT BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
);

ALTER TABLE DDL_LOG ADD PRIMARY KEY (ID);

CREATE SEQUENCE DDL_SEQ;

SET TERM ^ ;

CREATE OR ALTER TRIGGER TRIG_DDL_LOG_AFTER
ACTIVE AFTER ANY DDL STATEMENT POSITION 0
AS
BEGIN
INSERT INTO DDL_LOG (ID,
MOMENT,
USER_NAME,
EVENT_TYPE,
OBJECT_TYPE,
DDL_EVENT,
OBJECT_NAME,
SQL_TEXT)
VALUES (NEXT VALUE FOR DDL_SEQ,
CURRENT_TIMESTAMP,
CURRENT_USER,
RDB$GET_CONTEXT('DDL_TRIGGER', 'EVENT_TYPE'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_TYPE'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'DDL_EVENT'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_NAME'),
RDB$GET_CONTEXT('DDL_TRIGGER', 'SQL_TEXT'));
END^

SET TERM ; ^

---------------------------------

set term ^;

create procedure p1
as
begin
end^

set term ;^

comment on procedure p1 is 'my proc';

drop procedure p1;

ID MOMENT USER_NAME EVENT_TYPE OBJECT_TYPE DDL_EVENT OBJECT_NAME SQL_TEXT
1 04.03.2014 22:00:42 SYSDBA CREATE PROCEDURE CREATE PROCEDURE P1
2 04.03.2014 22:00:42 SYSDBA DROP PROCEDURE DROP PROCEDURE P1

[firebird-automations]

Modified by: @sim1984

summary: DDL_TRIGGER not fire on DDL operator COMMENT ON => DDL_TRIGGER not fire on DDL operator COMMENT ON, GRANT and REVOKE

[firebird-automations]

Commented by: @asfernandes

Behavior is now documented in README.ddl_triggers.

[firebird-automations]

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Commented by: @dyemanov

Adriano, while I tend to agree about COMMENT ON, I'm not so sure about the rest. Some sources describe GRANT / REVOKE as DCL (Data Control Language) but formally they're part of DDL. Shouldn't we support triggers for these commands? Or is it just an implementation issue?

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Dmitry,

Please clarify, do you mean that you agree that COMMENT ON should fire trigger?

[firebird-automations]

Commented by: @dyemanov

I'm not absolutely sure, but I can agree that COMMENT ON should *not* fire triggers, as it does not change the schema itself, only user-defined descriptions.

[firebird-automations]

Commented by: @asfernandes

It's not that is impossible, but grant is different from the others commands.

Event names may be:
GRANT ON TABLE (PROCEDURE, etc)
GRANT TO USER

Maybe has others differences and details I'm not seeing now.