In today’s article, we will explain PostgreSQL DDL Trigger and Event Trigger structures with the help of examples.
Data Definition Language (DDL) tables are used to define the database structure or schema.
Some of these are as follows.
Operations such as CREATE, ALTER, DROP and TRUNCATE.
Sometimes we want to keep a record of DDL operations.
Therefore, we have to use operations such as triggers.
First, we create the table where the ddl transactions will be kept.
|
1 |
create table ddl_log (islem char(100),yapılantarih char(100),kullanıcı char(100 )) |
After creating our table, let’s create our function.
|
1 2 3 4 5 |
CREATE OR REPLACE FUNCTION eventlog() RETURNS event_trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO ddl_log VALUES (tg_tag, NOW(), current_user); END; $$; |
Let’s create our event trigger.
|
1 2 |
CREATE EVENT TRIGGER ddllog ON ddl_command_end EXECUTE FUNCTION eventlog(); |
Let’s create a function called deneme.
|
1 2 3 4 5 6 7 8 9 |
create function deneme() returns char(100) language plpgsql as $$ begin raise info 'deneme '; end; $$; |
We check the ddl_log table we created, is our record added?
Let’s create a table for testing purposes.
|
1 |
create table deneme1(a int) |
