(a) Multiple target objects

For example, when SELECT * FROM "T1","T2" is executed, two audit trail lines are output, one for object T1 and one for T2, as shown below. In this case, the same termination code is set for both.

...	Event subtype	...	Object name	...	Termination code	...
...	SEL	...	T1	...	XXX	...
...	SEL	...	T2	...	XXX	...

An event, such as routine re-creation or the utility for reorganizing a database in units of schemas, might succeed or fail depending on the object. In this case, a different termination code is output for each object. The following table lists events that sometimes have multiple target objects.

Table 24-6 Events that sometimes have multiple target objects (event execution units and audit trail record output units)

Events	Execution unit	Target output units	Number of output records	SQL code or termination code
GRANT, REVOKE	User	Users	Number of target users	Same value for all event-target objects
	Role	Roles	Number of target roles
CREATE CONNECTION SECURITY, DROP CONNECTION SECURITY	Setting value of the connection security facility	Setting values of the connection security facility	Number of setting values of the connection security facility
Data manipulation SQL	Base table	Base table Sequence generator	Number of target base tables + number of target sequence generators
	View table	View tables Sequence generator	Number of target view tables + number of target sequence generators
ALTER ROUTINE, ALTER PROCEDURE, ALTER TRIGGER	Multiple routines or triggers#1	Procedures, functions, or triggers	One for each target routine	Different value for each event-target object
pdload	Base table RDAREA	Base table Sequence generator	Number of target base tables + number of target sequence generators
pdexp, pddefrev	Base table	Base tables	Number of target base tables
	Viewed table	View tables	Number of target view tables
	Stored procedure	Stored procedures	Number of target stored procedures
	Trigger	Triggers	Number of target trigger count
pdrorg	Schema,#2 base table, or index	Base tables	Number of target base tables
pdaudbegin, pdaudend	Unit	Units	Number of target units

#1: If routine information cannot be collected, NULL is entered for the object information and only a single audit trail line is output.

#2: In the case of reorganizing in units of schemas, if the table information in the schema cannot be collected, schema information is entered for the object information, and only a single audit trail line is output.

(b) Subqueries

For example, when UPDATE "T1" SET "C1" = (SELECT "C1" FROM "T2" WHERE CODE = '01') WHERE CODE = '01' is executed, information on the accessed objects is output as in (a) above. In this case, audit trails of the actions performed on the objects are output for the event type and subtypes as shown below. The same value is entered for the SQL code in the audit trail at each event's termination.

...	Event subtype	...	Object name	...	Termination code	...
...	UPD	...	T1	...	XXX	...
...	SEL	...	T2	...	XXX	...

In some cases, another SQL is executed as an extension of an executed SQL. If an SQL that is executed as an extension when an event is executed is defined as the audit target, information is collected on the target object of the SQL that is executed as an extension, or on the user. In this case, the event type and subtype of the SQL that is executed as an extension are entered as the event type and subtype of the object or user. Furthermore, for all objects, the same value is entered for the SQL code of each audit trail.

For example, if a table is deleted as an extension of DROP SCHEMA, object information on the table is output as the accessed object, separately from schema information. If the deletion process is terminated by an error, an audit trail is output for each user and each object that has been accessed up to the time of the error.

In procedures and triggers, an SQL is executed automatically as an extension of another SQL. In this case, an event termination audit trail is output for each SQL that is executed as an extension. In the case of a trigger, because the executor switches temporarily to the trigger owner, the trigger owner is entered as the event executor. In the case of a procedure, an event termination audit trail is also output when a CALL statement is terminated. If the procedure is nested, an event termination audit trail is output for each CALL statement. The following table shows audit trail record output units for trigger and procedure execution.

Table 24-7 Audit trail record output units for trigger and procedure execution

Type		Event executor (authorization identifier value)	Audit trail record output unit
Trigger	User trigger	Trigger owner	SQL units in the trigger
	System trigger (internal trigger of a referential constraint, for example)	Trigger owner	SQL units in the system trigger
Procedure		Procedure executor	SQL units in the procedure + CALL statement

If an error occurs in an SQL in a trigger or procedure, the same SQL record is output as the event results of the SQL at that point in time, the event results of the SQL that became the trigger, and the results of the CALL statement. Table 24-8 Error locations in a trigger and audit trail (SQL code) details shows the error locations in a trigger and the audit trail (SQL code) details. Table 24-9 Error locations in a procedure and audit trail (SQL code) details shows the error locations in a procedure and the audit trail (SQL code) details.

Table 24-8 Error locations in a trigger and audit trail (SQL code) details

BEFORE trigger SQL result [1]	Trigger SQL result [2]	AFTER trigger SQL result [3]	Audit trail (SQL code) details
			Audit trail of [1]	Audit trail of [2]	Audit trail of [3]
Error	--	--	SQL record of [1]	SQL record of [1]	N
Normal	Error	--		SQL record of [2]	N
	Normal	Error		SQL record of [3]	SQL record of [3]
		Normal	0	0	0

Legend:

--: Not applicable

N: No audit trail (SQL code) is output.

Table 24-9 Error locations in a procedure and audit trail (SQL code) details

Nesting?	Details of event for which audit trail is to be collected					Audit trail (SQL code) details
	[1]	[2]	[3]	[4]	[5]	Audit trail of [1]	Audit trail of [2]	Audit trail of [3]	Audit trail of [4]	Audit trail of [5]
No	E	--	--	--	E	SQL record of [1]	N	N	N	SQL record of [1]
	Nrm	--	--	--	E	0	N	N	N	SQL record of [5]
					Nrm	0	N	N	N	0
Yes	E	--	--	--	E	SQL record of [1]	N	N	N	SQL record of [1]
	Nrm	E	E	--	E	0	SQL record of [2]	SQL record of [2]	N	SQL record of [2]
		Nrm	E	--	E	0	0	SQL record of [3]	N	SQL record of [3]
			Nrm	E	E	0	0	0	SQL record of [4]	SQL record of [4]
				Nrm	E	0	0	0	0	SQL record of [5]
					Nrm	0	0	0	0	0

Legend:

--: Not applicable

E: Error

Nrm: Normal

N: No audit trail (SQL code) is output.

[1]: Result of SQL in a procedure before nesting

[2]: Result of SQL in a procedure after nesting

[3]: Result of a nested CALL statement

[4]: Result of SQL in a procedure before nesting

[5]: Result of a CALL statement

In the case of a dynamic SQL, the entire process from PREPARE to execution is treated as a single SQL, and an event termination audit trail is output for that unit. An event termination audit trail during execution is output when OPEN, CLOSE or EXECUTE is completed. The following figure shows the flow of a dynamic SQL depending on the type of data manipulation SQL.

Figure 24-3 Flow of a dynamic SQL depending on the type of data manipulation SQL

Explanation

The shaded SQLs are triggers for output of an audit trail. Note that the audit trail collection method for PREPARE, CLOSE and OPEN depends on where an error occurs. The following table shows whether an audit trail is output depending on the success or failure of events during dynamic SQL execution.

Table 24-10 Audit trail output depending on the success or failure of an event during dynamic SQL execution

SQL result			Collection specification
			Upon success	Upon failure	Both
PREPARE successful	OPEN (EXECUTE) successful	CLOSE successful	Output on CLOSE (EXECUTE)	Not output	Output on CLOSE (EXECUTE)
		CLOSE failed	Not output	Output on CLOSE (EXECUTE)	Output on CLOSE (EXECUTE)
	OPEN (EXECUTE) failed		Not output	Output on OPEN (EXECUTE)
PREPARE failed	--		Not output	Output on PREPARE
EXECUTE IMMEDIATE			Output on EXECUTE IMMEDIATE

Legend:

--: Not applicable

Note

An event termination audit trail is output when the cursor is closed by executing the CLOSE statement internally without having to execute it explicitly. For the timing, see the section on the CLOSE statement in the manual HiRDB Version 9 SQL Reference.