读书笔记 - Derby Reference Manual（三）

五、CREATE INDEX

　　Derby中最多支持16列索引键。

　　索引名最长为128个字符。

　　同一个索引中，每列只允许出庭一次；不同索引无此限制。

　　唯一性索引会对数据进行完整性检查。

　　表与对应索引应该在同一个schema下。

　　Derby默认使用升序对每一列建立索引。

　　当一个列存在唯一性、主键、外键约束时，系统则会自动帮其建立索引（俗称隐性索引），也就是说存在唯一性或者主键约束的列，不能再对其建立索引。而对存在唯一性索引的列添加唯一性或者主键约束，该列就会存在两个索引。可以通过约束名找到对应的隐性索引：
　　SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES,
　　SYS.SYSCONSTRAINTS WHERE
　　SYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID
　　AND CONSTRAINTNAME = constraint_name

　　索引键列的大小必须小于或者等于索引页大小的一半。如果对已存在数据的列建立索引，而其大超过前所述，则索引创建失败。在索引创建后，如果关联键列的大小超过页面大小，也会导致索引插入失败。(The size of the key columns in an index must be equal to or smaller than half the page size. If the length of the key columns in an existing row in a table is larger than half the page size of the index, creating an index on those key columns for the table will fail. This error only occurs when creating an index if an existing row in the table fails the criteria. After an index is created, inserts may fail if the size of their associated key exceeds the criteria.)

  当对一个表建立索引后，基于该表的Prepared statements将会失效，必须重新编译。而打开的游标可以继续使用，不受影响(Prepared statements that involve SELECT, INSERT, UPDATE, UPDATE WHERE CURRENT, DELETE, and DELETE WHERE CURRENT on the table referenced by the CREATE INDEX statement are invalidated when the index is created. Open cursors on the table are not affected.)。

六、CREATE ROLE

  仅限数据库创建者拥有创建角色(ROLE)的权限，且需将数据属性derby.database.sqlAuthorization设置为true。

　　Derby尽量避免数据库用户名与角色名冲突。当一个用户名与角色名相同时，该用户是不允许连接上数据库的。

　　角色名不能以SYS或者sys开头，并且不能为单词PUBLIC或者public。

七、CREATE SCHEMA

  仅限数据库创建者拥有创建schema的权限，且需将数据属性derby.database.sqlAuthorization设置为true。

　　与SQL标准不同的是，Derby只允许用户名作为AUTHORIZATION的参数，角色名是不允许的。

  以SYS/sys开头的schema都属于系统级别的schema。

八、CREATE SYNONYM

　　可以为同一个schema或者不同schema下的表和视图创建同义语，甚至为其它同义语再建同义语（但不允许创建死循环同义语:synonym A -> synonym B; synonym C -> synonym A; synonym B -> synonym C;）。

　　不允许为不存在的表或者视图创建同义语。

　　同义语与表、视图共享一个命名空间，所以同一个schema下，同义语、表、视图之间不允许出现同名。

　　不允许在系统schema和临时表中创建同义语。

九、CREATE TABLE

　　可以通过处理过程SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY来更改存储属性，比如表的页面大小。

　　CREATE TABLE ... AS ... 可以以query的结果集中列名和数据类型或者仅数据类型来作为模板建立新表。不指定新表的列名，则完全以结果集的列与数据类型建立新表；指定列名，则以结果集的数据类型为模板建立新表（不过须注意列数一定相等）。目前必须指定WITH NO DATA，即不导入qury结果集的实际数据。Derby或许在将来会加入WITH DATA，这样就可以在生成新表的同时将数据导入，期待吧。

十、CREATE TRIGGER

　　触发器没有数量限制，甚至允许为同一个表的同一个事件建立多个触发器。

　　触发器分为事前与事后触发器：事前即在事件发生前，语句和约束被执行前实行触发器的动作；事后则是语句与约束执行后再实行触发器动作。

　　目前，触发器捕捉以下三类事件：INSERT、UPDATE、DELETE

Referencing old and new values: the referencingclause

Many triggered-SQL-statements need to refer to data thatis currently being changed by the database event that caused them to fire.The triggered-SQL-statement might need to refer to the new (post-change or"after") values.

Derby providesyou with a number of ways to refer to data that is currently being changedby the database event that caused the trigger to fire. Changed data can bereferred to in the triggered-SQL-statement using transition variables or transitiontables. The referencing clause allows you to provide a correlation nameor alias for these transition variables by specifying OLD/NEW AS correlation-Name .

Forexample, if you add the following clause to the trigger definition:

REFERENCING OLD AS DELETEDROW

youcan then refer to this correlation name in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id

The OLD and NEW transition variables map to a java.sql.ResultSet witha single row.

Note: Only row triggers (see Statement versus row triggers)can use the transition variables. INSERT row triggers cannot reference anOLD row. DELETE row triggers cannot reference a NEW row.

Forstatement triggers, transition tables serve as a table identifier forthe triggered-SQL-statement or the trigger qualification. The referencingclause allows you to provide a correlation name or alias for these transitiontables by specifying OLD_TABLE/NEW_TABLE AS correlation-Name

For example:

REFERENCING OLD_TABLE AS DeletedHotels

allowsyou to use that new identifier (DeletedHotels) in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id IN
    (SELECT hotel_id FROM DeletedHotels)

The old andnew transition tables map to a java.sql.ResultSet with cardinalityequivalent to the number of rows affected by the triggering event.

Note: Onlystatement triggers (see Statement versus row triggers)can use the transition tables. INSERT statement triggers cannot referencean OLD table. DELETE statement triggers cannot reference a NEW table.

Thereferencing clause can designate only one new correlation or identifier andonly one old correlation or identifier. Row triggers cannot designate an identifierfor a transition table and statement triggers cannot designate a correlationfor transition variables.

Statement versus row triggers

Youhave the option to specify whether a trigger is a statement trigger or a row trigger.If it is not specified in the CREATE TRIGGER statement via FOR EACH clause, then the trigger is a statement trigger by default.

• statement triggers

  A statement trigger fires once per triggeringevent and regardless of whether any rows are modified by the insert, update,or delete event.

• row triggers

  A row trigger fires once for each row affectedby the triggering event. If no rows are affected, the trigger does not fire.

Note: An update that sets a column value to the value that it originallycontained (for example, UPDATE T SET C = C) causes a row trigger to fire,even though the value of the column is the same as it was prior to the triggeringevent.

Triggered-SQL-statement

The actiondefined by the trigger is called the triggered-SQL-statement (in Syntax above,see the last line). It has the following limitations:

• It must not contain any dynamic parameters (?).
• It must not create, alter, or drop the table upon which the trigger isdefined.
• It must not add an index to or remove an index from the table on whichthe trigger is defined.
• It must not add a trigger to or drop a trigger from the table upon whichthe trigger is defined.
• It must not commit or roll back the current transaction or change theisolation level.
• Before triggers cannot have INSERT, UPDATE or DELETE statements as theiraction.
• Before triggers cannot call procedures that modify SQL data as their action.
• The NEW variable of a Before trigger cannot reference a generated column.

The triggered-SQL-statement can reference database objects otherthan the table upon which the trigger is declared. If any of these databaseobjects is dropped, the trigger is invalidated. If the trigger cannot be successfullyrecompiled upon the next execution, the invocation throws an exception andthe statement that caused it to fire will be rolled back.

For more informationon triggered-SQL-statements, see the Derby Developer's Guide.

Order of execution

When a database event occursthat fires a trigger, Derby performsactions in this order:

• It fires No Cascade Before triggers.
• It performs constraint checking (primary key, unique key, foreign key,check).
• It performs the insert, update, or delete.
• It fires After triggers.

When multiple triggers are defined for the same database eventfor the same table for the same trigger time (before or after), triggers arefired in the order in which they were created.

-- Statements and triggers:

CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
  FOR EACH ROW MODE DB2SQL
  values app.notifyEmail('Jerry', 'Table x is about to be updated'); 

CREATE TRIGGER FLIGHTSDELETE
  AFTER DELETE ON FLIGHTS
  REFERENCING OLD_TABLE AS DELETEDFLIGHTS
  FOR EACH STATEMENT 
  DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
  (SELECT FLIGHT_ID FROM DELETEDFLIGHTS);

CREATE TRIGGER FLIGHTSDELETE3
  AFTER DELETE ON FLIGHTS
  REFERENCING OLD AS OLD
  FOR EACH ROW 
  DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;

Note: You can find more examples in the Derby Developer's Guide.

Trigger recursion

The maximum trigger recursiondepth is 16.