External Table Example

For our example, we will define an external log table that might be used by an exception handler in a stored procedure or trigger.The external table is chosen because the messages from any handled exceptions will be retained in the log, even if the transaction that launched the process is eventually rolled back because of another, unhandled exception.For demonstration purposes, it has two data columns, a timestamp and a message.The third column stores the row delimiter:

CREATE TABLE ext_log
  EXTERNAL FILE 'd:\externals\log_me.txt' (
  stamp CHAR (24),
  message CHAR(100),
  crlf CHAR(2) -- for a Windows context
);
COMMIT;

Now, a trigger, to write the timestamp and the row delimiter each time a message is written to the file:

SET TERM ^;
CREATE TRIGGER bi_ext_log FOR ext_log
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (new.stamp is NULL) then
    new.stamp = CAST (CURRENT_TIMESTAMP as CHAR(24));
  new.crlf = ASCII_CHAR(13) || ASCII_CHAR(10);
END ^
COMMIT ^
SET TERM ;^

Inserting some records (which could have been done by an exception handler or a fan of Shakespeare):

insert into ext_log (message)
values('Shall I compare thee to a summer''s day?');
insert into ext_log (message)
values('Thou art more lovely and more temperate');

The output:

2015-10-07 15:19:03.4110Shall I compare thee to a summer's day?
2015-10-07 15:19:58.7600Thou art more lovely and more temperate

Setting a `DEFAULT` Value

The optional DEFAULT clause allows you to specify the default value for the table column.This value will be added to the column when an INSERT statement is executed and that column was omitted from the INSERT command or DEFAULT was used instead of a value expression.The default value will also be used in UPDATE when DEFAULT is used instead of a value expression.

The default value can be a literal of a compatible type, a context variable that is type-compatible with the data type of the column, or NULL, if the column allows it.If no default value is explicitly specified, NULL is implied.

An expression cannot be used as a default value.

External Table Example

External Table Example

Setting a DEFAULT Value

Setting a `DEFAULT` Value