Derby 触发器
在数据库中，触发器是在事件发生时执行的语句/代码。一旦您为表上的特定事件创建触发器，每次发生事件时都会执行触发器中指定的代码。您可以在单个表上创建多个触发器。
本章教您如何使用 Apache Derby 创建和删除触发器。
创建触发器
您可以在 Derby 中使用 CREATE TRIGGER 语句创建触发器。
语法
以下是 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, 'Archana', 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 {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:TestDataBase;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating the Emp table
stmt.execute("CREATE TABLE Emp ( "
+ "Id int NOT null, "
+ "Name VARCHAR(255), "
+ "Salary int NOT null, "
+ "Location VARCHAR(255))");
//Insert values in to the EMp table
String query = "INSERT INTO Emp(Id, Name, Salary, Location) VALUES \r\n"
+"(1, 'Amit', 30000, 'Hyderabad'), "
+ "(2, 'Kalyan', 40000, 'Vishakhapatnam'), "
+ "(3,'Renuka', 50000, 'Delhi'), "
+ "(4, 'Archana', 15000, 'Mumbai'), "
+ "(5, 'Trupthi', 45000, 'Kochin')";
stmt.execute(query);
//Creating the BackUp table
stmt.execute("CREATE TABLE BackUp ( "
+ "Id int NOT null, "
+ "Name VARCHAR(255), "
+ "Salary int NOT null, "
+ "Location VARCHAR(255))");
//Creating a trigger
String createTrigger = "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)";
stmt.execute(createTrigger);
System.out.println("Trigger created");
//Deleting records from Emp table
stmt.executeUpdate("Delete From Emp where Name = 'Kalyan'");
stmt.executeUpdate("Delete From Emp where Name = 'Amit'");
//Getting the contents of BackUp table
ResultSet rs = stmt.executeQuery("SELECT * from BackUp");
while(rs.next()){
System.out.println(rs.getInt("Id"));
System.out.println(rs.getString("Name"));
System.out.println(rs.getString("Salary"));
System.out.println(rs.getString("Location"));
System.out.println(" ");
}
}
}
输出
在执行上述程序时，会生成以下输出-
Trigger created
2
Kalyan
40000
Vishakhapatnam
1
Amit
30000
Hyderabad
