Best practices for creating triggers

Avoid table scans in database triggers

Table scans are expensive operations and can occur when SQL statements such as FOR EACH ROW are applied to a database table. In a database trigger, the cost of these scans is high if the trigger fires frequently and if the table has a large number of rows. For example, if you change the deduplication trigger on the alerts.status table that every time the trigger fires it scans alerts.status for rows matching a set of criteria. In this case, the scalability of the system is limited, because the database trigger takes longer and longer as the number of rows that need to be scanned increases. Also avoid nested scans.

Avoid the EVALUATE clause

When a trigger contains an EVALUATE clause, a temporary table is created to hold the results of the SELECT statement in the EVALUATE clause. The amount of time and resources that this temporary table consumes depends on the number of columns that are selected and the number of rows matched by the condition in the WHERE clause.

In most cases, you can replace the EVALUATE clause with a FOR EACH ROW clause. The FOR EACH ROW clause reads over the data and does not incur the overhead of creating a temporary table.

A suitable use for an EVALUATE clause is when a GROUP BY clause is being applied to an SQL query.

Avoid excessive use of the WRITE INTO statement for logging out to file

When triggers is deployed in production environments, comment out or remove WRITE INFO statements. It is advisable to limit the use of WRITE INFO statements to development environments, for debugging triggers. The quantity of data that is logged during debugging might cause a bottleneck in production environments.

Determine what is suitable for your system. For example, if the logging is infrequently called, there is probably no issue. However, if logging is called multiple times per INSERT statement (for example, within a nested loop), there could be a bottleneck.

Where possible, use the primary key when modifying rows

If the primary key of a database table is used in the WHERE clause of an UPDATE statement, the row is accessed by using direct lookup, rather than a table scan. For example:

update alerts.status where Identifier = tt.Identifier set Severity = Severity + 1;

Use indexes when using lookup tables

The ObjectServer uses an index to access rows in a table if the primary key is used in a FOR EACH ROW statement.

This functionality is most useful where an ObjectServer table is being used as a lookup table, for example to enrich events. In such a case, design the table and triggers to access the lookup table by its primary keys, to prevent costly full table scans. For example:

create table alerts.iplookup persistent
(
	IpAddr		varchar(32) primary key,
	HostName	varchar(8),
	Owner		varchar(40)
);

create or replace trigger set_hostname
group madeup_triggers
priority 10
before insert on alerts.status
for each row
begin
	-- Access the lookup table using the primary key
	for each row tt in alerts.iplookup where tt.IpAddr = new.Node
	begin
		set new.Hostname = tt.HostName;
	end;
end;

Use the generic_clear trigger as a basis for correlation type triggers

The standard generic_clear trigger (see $NCHOME/omnibus/etc/automation.sql) correlates resolution events with their related problem events. After this trigger runs, all matched rows have their severity set to 0, in readiness for removal by the delete_clears automation. If you need different types of correlation trigger, base these triggers on the generic_clear trigger.

The standard generic_clear trigger does not use the EVALUATE clause to select the events. Instead it uses the FOR EACH ROW construct to loop over the events to populate a temporary table with the problem events. Because this temporary table contains only a subset of the events in the alerts.status table, the cost of the update operation that is applied to relate the problems with resolutions is reduced. Additionally, because the identifier of the problem event is stored in the temporary table, the problem events can be updated directly in alerts.status by using the UPDATE VIA command to perform a direct lookup on the row; this takes advantage of the Identifier field being a primary key.

Use deduplication to clear events where possible

This approach reduces the amount of processing for the generic_clear trigger to perform. It leaves the trigger to resolve cases where a single resolution event can clear many problem events.

Notes on creating automations

Test your changes