computer science

Log trigger

Log trigger is a computer science topic covered in the lgStudy science library. This page brings together a partial reference excerpt, illustrations, worked examples, real-world applications and a short study plan, so you can understand Log trigger rather than just read about it. In short: In relational databases, the log trigger or history trigger is a mechanism for automatic recording of information about changes inserting or/and updating or/and deleting rows in a database table. It is a particular technique for change data capturing, and in data warehousing for dealing with slowly changing dimensions.

Key takeaways

Log trigger belongs to computer science; place it in that map before memorising details.
Learn the definition first, then one example that makes the definition concrete.
Connect Log trigger to a quantity you can measure, compute or draw — that is where exam questions come from.
Reproduce the core statement of Log trigger from memory before moving on to harder problems.

Reference excerpt

In relational databases, the log trigger or history trigger is a mechanism for automatic recording of information about changes inserting or/and updating or/and deleting rows in a database table. It is a particular technique for change data capturing, and in data warehousing for dealing with slowly changing dimensions.

Introduction Operational databases are typically designed to capture the current state of an organization, acting as a snapshot of "now" rather than a historical archive. In this environment, updates are often destructive; when a specific data point changes, the system prioritizes efficiency by replacing the existing value with the new one. For instance, in an employee or customer directory, if an individual moves to a new location, an update operation is performed on the database that writes the new address directly over the old one. Consequently, the previous address is permanently overwritten and lost to the system, leaving the database with only the most up-to-date information and no record of the entity's history or previous status. The Log trigger is a mechanism to automatically detect changes and to store the previous status of information.

Definition Suppose there is a table which we want to audit. This table contains the following columns: Column1, Column2, ..., Columnn The column Column1 is assumed to be the primary key. These columns are defined to have the following types: Type1, Type2, ..., Typen The Log Trigger works writing the changes (INSERT, UPDATE and DELETE operations) on the table in another, history table, defined as following:

As shown above, this new table contains the same columns as the original table, and additionally two new columns of type DATETIME: StartDate and EndDate. This is known as tuple versioning. These two additional columns define a period of time of "validity" of the data associated with a specified entity (the entity of the primary key), or in other words, it stores how the data were in the period of time between the StartDate (included) and EndDate (not included). For each entity (distinct primary key) on the original table, the following structure is created in the history table. Data is shown as example.

Notice that if they are shown chronologically the EndDate column of any row is exactly the StartDate of its successor (if any). It does not mean that both rows are common to that point in time, since -by definition- the value of EndDate is not included. There are two variants of the Log trigger, depending how the old values (DELETE, UPDATE) and new values (INSERT, UPDATE) are exposed to the trigger (it is RDBMS dependent): Old and new values as fields of a record data structure

Old and new values as rows of virtual tables

Compatibility notes The code above is shown as a code idiom. Trigger syntax vary enormously among RDBMS, for example:

The function GetDate() is used to get the system date and time, a specific RDBMS could either use another function name, or get this information by another way. Several RDBMS (Db2, MySQL) do not support that the same trigger can be attached to more than one operation (INSERT, DELETE, UPDATE). In such a case a trigger must be created for each operation; For an INSERT operation only the inserting section must be specified, for a DELETE operation only the deleting section must be specified, and for an UPDATE operation both sections must be present, just as it is shown above (the deleting section first, then the inserting section), because an UPDATE operation is logically represented as a DELETE operation followed by an INSERT operation. In the code shown, the record data structure containing the old and new values are called OLD and NEW. On a specific RDBMS they could have different names. In the code shown, the virtual tables are called DELETED and INSERTED. On a specific RDBMS they could have different names. Another RDBMS (Db2) even let the name of these logical tables be specified. In the code shown, comments are in C/C++ style, they could not be supported by a specific RDBMS, or a different syntax should be used. Several RDBMS require that the body of the trigger is enclosed between BEGIN and END keywords.

Implementation in common RDBMS

IBM Db2 Source:

A trigger cannot be attached to more than one operation (INSERT, DELETE, UPDATE), so a trigger must be created for each operation. The old and new values are exposed as fields of a record data structures. The names of these records can be defined, in this example they are named as O for old values and N for new values.

Microsoft SQL Server Source:

The same trigger can be attached to all the INSERT, DELETE, and UPDATE operations. Old and new values as rows of virtual tables named DELETED and INSERTED.

MySQL A trigger cannot be attached to more than one operation (INSERT, DELETE, UPDATE), so a trigger must be created for each operation. The old and new values are exposed as fields of a record data structures called Old and New.

Oracle The same trigger can be attached to all the INSERT, DELETE, and UPDATE operations. The old and new values are exposed as fields of a record data structures called :OLD and :NEW. It is necessary to test the nullity of the fields of the :NEW record that define the primary key (when a DELETE operation is performed), in order to avoid the insertion of a new row with null values in all columns.

PostgreSQL The action associated to a trigger must be specified as a function, so a function is first defined. Old and new values are exposed as rows of virtual tables named old_table and new_table, but these names can be different. Even though a trigger can be associated to more than one operation (INSERT, DELETE, UPDATE), in this case a different trigger is associated on every operation on order to specify the names of the virtual tables, and these triggers can reference to the same function.

… excerpt ends here. Continue reading the full article.

Worked examples

Example 1 — a first encounter with Log trigger

Start with the simplest possible case. Write down what Log trigger claims or describes in one sentence, then invent the smallest concrete situation in which that sentence is true. In computer science, the smallest case is usually a single object, a single equation or a single measurement. Check that every symbol or term in your sentence has a meaning in that case.

Example 2 — changing one variable

Take the situation from Example 1 and change exactly one quantity: double it, halve it, or set it to zero. Predict what should happen to Log trigger before you calculate. Comparing your prediction with the result is the fastest way to find out whether you understand the idea or only the words.

Example 3 — an exam-style question

Typical questions about Log trigger ask you to (a) state it precisely, (b) apply it to given data, and (c) explain a limitation. Practise writing all three answers in under five minutes; the third part is what separates a full-mark answer from an average one.

Applications of Log trigger

In research: Log trigger appears in computer science research whenever the underlying quantities have to be modelled precisely. Papers usually cite it as a starting assumption and then explore where it breaks down.
In technology and industry: Engineering practice reuses Log trigger in design rules, simulations and safety margins. Knowing the idea lets you read a specification sheet and understand why the numbers look the way they do.
In the classroom: Log trigger is common in secondary-school and first-year university syllabi. It links to neighbouring topics Computer data, Data management, Data modeling, so understanding it makes those chapters shorter.
In everyday life: Look for Log trigger outside the textbook — in sport, cooking, traffic, electronics or the sky above you. An example you found yourself is remembered far longer than one you were given.

Affiliate

Preply — study more efficiently by working with a personal tutor. 50% off.

How to study Log trigger in 20 minutes

Read the reference excerpt below once, without taking notes.
Close the page and write down what Log trigger means in your own words.
Compare your version with the excerpt and mark what you missed.
Work through the three examples above with pen and paper.
Explain Log trigger out loud to somebody else — or to Teacher Smith in the lgStudy chat.

Frequently asked questions

What is Log trigger in simple terms?

Why does Log trigger matter?

Because it connects several computer science ideas at once: it gives you a definition you can apply, a quantity you can calculate, and a way to check whether a result is plausible.

How should I study Log trigger?

Read the excerpt, restate it from memory, then work through the examples and applications listed on this page. The five-step study plan above takes about twenty minutes.

What does this page cover?

It gives you a compact reference excerpt plus original lgStudy explanations, examples, applications and study material on Log trigger.