(8.0.18)
サポートしていない模様
(12cR1)
DDL文(CREATE,ALTER,DROP,TRUNCATE等)
データベース操作文(LOGON,LOGOFF等)
drop table tab1 purge;
create table tab1(
col1 timestamp -- sydate
,col2 varchar2(100) -- ora_sysevent
,col3 varchar2(100) -- ora_login_user
,col4 number -- ora_instance_num
,col5 varchar2(100) -- ora_database_name
,col6 varchar2(100) -- ora_dict_obj_type
,col7 varchar2(100) -- ora_dict_obj_owner
,col8 varchar2(100) -- ora_dict_obj_name
,col9 varchar2(100) -- ora_client_ip_address
);
drop trigger trig1;
create or replace trigger trig1
after create or alter or drop or truncate on database
begin
insert into tab1
values(
sysdate
,ora_sysevent
,ora_login_user
,ora_instance_num
,ora_database_name
,ora_dict_obj_type
,ora_dict_obj_owner
,ora_dict_obj_name
,null
);
end;
/
drop trigger trig2;
create or replace trigger trig2
after logon on database
begin
insert into tab1
values(
sysdate
,ora_sysevent
,ora_login_user
,ora_instance_num
,ora_database_name
,null
,null
,null
,ora_client_ip_address
);
end;
/
drop trigger trig3;
create or replace trigger trig3
before logoff on database
begin
insert into tab1
values(
sysdate
,ora_sysevent
,ora_login_user
,ora_instance_num
,ora_database_name
,null
,null
,null
,null
);
end;
/
(12)
https://www.postgresql.jp/document/12/html/event-trigger-definition.html
drop table tab1;
create table tab1(
col1 timestamp -- clock_timestamp
,col2 oid -- classid
,col3 oid -- objid
,col4 integer -- objsubid
,col5 text -- command_tag
,col6 text -- object_type
,col7 text -- schema_name
,col8 text -- object_identity
,col9 bool -- in_extension
);
drop table tab2;
create table tab2(
col1 timestamp -- clock_timestamp
,col2 oid -- classid
,col3 oid -- objid
,col4 integer -- objsubid
,col5 bool -- original
,col6 bool -- normal
,col7 bool -- is_temporary
,col8 text -- object_type
,col9 text -- schema_name
,col10 text -- object_name
,col11 text -- object_identity
,col12 text -- address_names
,col13 text -- address_args
);
create or replace function fun1()
returns event_trigger
language plpgsql as $$
declare
rec record;
begin
for rec in select * from pg_event_trigger_ddl_commands()
loop
insert into tab1
values(
clock_timestamp()
,rec.classid
,rec.objid
,rec.objsubid
,rec.command_tag
,rec.object_type
,rec.schema_name
,rec.object_identity
,rec.in_extension
);
end loop;
end
$$;
create or replace function fun2()
returns event_trigger
language plpgsql as $$
declare
rec record;
begin
for rec in select * from pg_event_trigger_dropped_objects()
loop
insert into tab2
values(
clock_timestamp()
,rec.classid
,rec.objid
,rec.objsubid
,rec.original
,rec.normal
,rec.is_temporary
,rec.object_type
,rec.schema_name
,rec.object_name
,rec.object_identity
,rec.address_names
,rec.address_args
);
end loop;
end
$$;
drop event trigger trig1;
create event trigger trig1
on ddl_command_end
execute function fun1()
;
drop event trigger trig2;
create event trigger trig2
on sql_drop
execute function fun2()
;
- SQL Server
(2019)
https://docs.microsoft.com/ja-jp/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15
https://docs.microsoft.com/ja-jp/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver15
DDLトリガー(CREATE,ALTER,DROP等)
ログオントリガー
use test;
go
drop table tab1;
create table tab1(
col1 datetime -- getdate
,col2 nvarchar(100) -- current_user
,col3 nvarchar(100) -- eventtype
,col4 nvarchar(2000) -- tsqlcommand
);
drop trigger trig1 on database;
create or alter trigger trig1
on database
after create_table,alter_table,drop_table
as
declare @data xml
set @data = eventdata()
insert into tab1
values(
getdate()
,convert(nvarchar(100), current_user)
,@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
,@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
);
use master;
go
drop table tab2;
create table tab2(
col1 datetime -- getdate
,col2 nvarchar(100) -- current_user
);
drop trigger trig2 on all server;
create or alter trigger trig2
on all server with execute as 'MMM064\Administrator'
after logon
as
insert into tab2
values(
getdate()
,convert(nvarchar(100), original_login() )
);
