Database Management · Intermediate level

Intermediate level · Module 4: Stored Logic and Data Import · Lesson 3 of 3

Functions, Triggers and Exports

Use database functions and triggers for small, well-defined jobs, and export data for others.

In this lesson you will

  • Write a simple function and an audit trigger
  • Export query results for reports and other systems

Key concepts

SQL functiontriggeraudit logexport to CSV

The lesson

Functions package logic in the database, such as a function that formats a household code. Procedures run several statements. Use them for logic that must be the same for every application, and keep them small and documented.

A trigger runs automatically when rows change. An audit trigger can record who changed which household and when in an audit table — useful for accountability. Avoid hiding major business rules in triggers, where application developers may not see them.

Export results with COPY (SELECT …) TO a CSV file or through the graphical tool, choosing UTF-8 encoding so names with any characters are preserved.

Step by step

  1. Write a function that builds a household code.
  2. Create an audit table and a trigger that fills it.
  3. Update a household and check the audit log.
  4. Export a report to CSV.

Practice activity

Add an audit trigger to the households table and export the donor report as CSV.

Expected result: Every change to a household appears in the audit log and the CSV opens correctly in a spreadsheet.

Check your understanding

  1. What is a trigger?

    Show answer

    Code the database runs automatically when rows change.

  2. Why use UTF-8 for exports?

    Show answer

    So names and text with any characters are preserved correctly.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.