无涯教程-Derby - 触发器(Triggers)

无涯教程
66 阅读3分钟

在数据库中，触发器只要事件发生，该语句就会自动执行，在表上为特定事件创建触发器后，每次事件发生时都会执行触发器中指定的代码，您可以在单个表上创建多个触发器。

本章教您如何使用Apache Derby创建和删除触发器。

创建触发器

您可以使用CREATE TRIGGER语句在Derby中创建触发器。

以下是CREATE TRIGGER查询的语法。

CREATE TRIGGER trigger_name
{ NO CASCADE BEFORE | AFTER }
{INSERT [OR] | UPDATE [OR] | DELETE}[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
Statement

假设无涯教程在Derby中创建了一个名为Emp的表，如下所示。

CREATE TABLE Emp (
   Id INT NOT NULL,
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255) );

并在其中插入5行。

INSERT INTO Emp(Id, Name, Salary, Location) VALUES
(1, Amit, 30000, Hyderabad), (2, Kalyan, 40000, Vishakhapatnam),
(3,Renuka, 50000, Delhi), (4, Learnfk, 15000, Mumbai), (5, Trupthi,
45000, Kochin);

如果无涯教程有另一个名为BackUp的表，无涯教程的目的是在其中存储从Emp表删除的行。

CREATE TABLE BackUp (
   Id INT NOT NULL,
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255)
);

以下查询在名为 Emp 的DELETE查询表上创建一个触发器，它将已删除的 Emp 行存储到表Backup中。

ij> CREATE TRIGGER my_trigger
AFTER DELETE ON Emp
REFERENCING OLD AS oldRow
FOR EACH ROW MODE DB2SQL
INSERT INTO BackUp
VALUES (oldRow.Id, oldRow.Name, oldRow.Salary, oldRow.Location);

现在，从Emp表中删除一行-

ij> Delete From Emp where Name=Kalyan;
1 row inserted/updated/deleted
ij> Delete From Emp where Name=Amit;
1 row inserted/updated/deleted

如果您验证了备份表，则可以观察其中的已删除行。

ij> select * from BackUp;
ID |NAME |SALARY |LOCATION
-------------------------------------------------------------------------
2 |Kalyan |40000 |Vishakhapatnam
1 |Amit |30000 |Hyderabad

2 rows selected

删除触发器

您可以使用DROP TRIGGER语句在Derby中删除触发器。

以下是DROP TRIGGER查询的语法-

ij> Drop trigger tigger_name;

以下示例删除上面创建的触发器my_trigger-

ij> Drop trigger my_trigger;
0 rows inserted/updated/deleted

JDBC示例

以下JDBC程序在Derby中创建和删除触发器。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Triggers_Example {
   public static void main(String args[]) throws SQLException, ClassNotFoundException {
      //注册驱动
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      //创建连接
      String URL="jdbc:derby:TestDataBase;create=true";
      Connection conn=DriverManager.getConnection(URL);

  </span><span class="com">//获取Statement对象</span><span class="pln">
  </span><span class="typ">Statement</span><span class="pln"> stmt</span><span class="pun">=</span><span class="pln">conn</span><span class="pun">.</span><span class="pln">createStatement</span><span class="pun">();</span><span class="pln">

  </span><span class="com">//创建表结构</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"CREATE TABLE Emp ( "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Id INT NOT NULL, "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Name VARCHAR(255), "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Salary INT NOT NULL, "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Location VARCHAR(255))"</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//插入一些测试数据</span><span class="pln">
  </span><span class="typ">String</span><span class="pln"> query</span><span class="pun">=</span><span class="str">"INSERT INTO Emp(Id, Name, Salary, Location) VALUES\r\n"</span><span class="pln">
     </span><span class="pun">+</span><span class="str">"(1, Amit, 30000, Hyderabad), "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"(2, Kalyan, 40000, Vishakhapatnam), "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"(3,Renuka, 50000, Delhi), "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"(4, Learnfk, 15000, Mumbai), "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"(5, Trupthi, 45000, Kochin)"</span><span class="pun">;</span><span class="pln">
     stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="pln">query</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//创建备份表</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"CREATE TABLE BackUp ( "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Id INT NOT NULL, "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Name VARCHAR(255), "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Salary INT NOT NULL, "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Location VARCHAR(255))"</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//创建触发器</span><span class="pln">
  </span><span class="typ">String</span><span class="pln"> createTrigger</span><span class="pun">=</span><span class="str">"CREATE TRIGGER my_trigger "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"AFTER DELETE ON Emp "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"REFERENCING OLD AS oldRow "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"FOR EACH ROW MODE DB2SQL "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"INSERT INTO BackUp "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"VALUES (oldRow.Id, oldRow.Name, oldRow.Salary, oldRow.Location)"</span><span class="pun">;</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="pln">createTrigger</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Trigger created"</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//删除Name=Kalyan or Name=Amit的数据</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">executeUpdate</span><span class="pun">(</span><span class="str">"Delete From Emp where Name=Kalyan"</span><span class="pun">);</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">executeUpdate</span><span class="pun">(</span><span class="str">"Delete From Emp where Name=Amit"</span><span class="pun">);</span><span class="pln">
  </span><span class="com">//获取备份表的数据</span><span class="pln">
  </span><span class="typ">ResultSet</span><span class="pln"> rs</span><span class="pun">=</span><span class="pln">stmt</span><span class="pun">.</span><span class="pln">executeQuery</span><span class="pun">(</span><span class="str">"SELECT * from BackUp"</span><span class="pun">);</span><span class="pln">
  </span><span class="kwd">while</span><span class="pun">(</span><span class="pln">rs</span><span class="pun">.</span><span class="kwd">next</span><span class="pun">()){</span><span class="pln">
     </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="pln">rs</span><span class="pun">.</span><span class="pln">getInt</span><span class="pun">(</span><span class="str">"Id"</span><span class="pun">));</span><span class="pln">
     </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="pln">rs</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Name"</span><span class="pun">));</span><span class="pln">
     </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="pln">rs</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Salary"</span><span class="pun">));</span><span class="pln">
     </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="pln">rs</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Location"</span><span class="pun">));</span><span class="pln">
     </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">" "</span><span class="pun">);</span><span class="pln">
  </span><span class="pun">}</span><span class="pln">



}
}

执行上述程序时，将生成以下输出-

Trigger created
2
Kalyan
40000
Vishakhapatnam
1
Amit
30000
Hyderabad

参考链接

www.learnfk.com/derby/apach…

avatar
文章
阅读
粉丝