Trigger function creation in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a trigger function in PostgreSQL?

A trigger function is a special function that automatically runs when a specified event happens on a table, like inserting, updating, or deleting data.

beginner

Which language is commonly used to write trigger functions in PostgreSQL?

PL/pgSQL is the most common language used to write trigger functions in PostgreSQL because it allows procedural logic inside the database.

beginner

What keyword is used to create a trigger function in PostgreSQL?

The keyword used is CREATE FUNCTION, followed by the function name and the trigger return type.

intermediate

What must a trigger function return in PostgreSQL?

A trigger function must return a special type called TRIGGER to work properly with triggers.

intermediate

How do you access the row data inside a trigger function during an INSERT event?

You use the special variable NEW to access the new row data being inserted.

Which statement correctly starts a trigger function in PostgreSQL?

A CREATE FUNCTION my_trigger() RETURNS TRIGGER AS $$ BEGIN ... END; $$ LANGUAGE plpgsql;

B CREATE TRIGGER my_trigger() BEGIN ... END;

C CREATE PROCEDURE my_trigger() RETURNS VOID AS $$ ... $$;

D CREATE FUNCTION my_trigger() RETURNS VOID AS $$ ... $$;

Trigger functions must be created with CREATE FUNCTION and must return type TRIGGER.

What does the NEW keyword represent inside a trigger function?

A The old row before update or delete

B The new row being inserted or updated

C The table name

D The trigger event type

NEW holds the new row data for INSERT or UPDATE events.

Which event can a trigger function respond to?

A INSERT

B UPDATE

C DELETE

D All of the above

Triggers can be set to fire on INSERT, UPDATE, or DELETE events.

What language must be specified when creating a trigger function in PostgreSQL?

A SQL

B Python

C plpgsql

D JavaScript

plpgsql is the procedural language used for writing trigger functions.

What is the return type of a trigger function?

A TRIGGER

B INTEGER

C VOID

D BOOLEAN

Trigger functions must return type TRIGGER.

Explain how to create a basic trigger function in PostgreSQL that logs inserts on a table.

Think about the function structure and what it should return.

Describe the role of trigger functions and how they interact with database events.

Consider what happens when data changes in a table.