在数据库中,触发器只要事件发生,该语句就会自动执行,在表上为特定事件创建触发器后,每次事件发生时都会执行触发器中指定的代码,您可以在单个表上创建多个触发器。
本章教您如何使用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中删除触发器。
链接:https://www.learnfk.comhttps://www.learnfk.com/derby/apache-derby-triggers.html
来源:LearnFk无涯教程网
以下是DROP TRIGGER查询的语法-
ij> Drop trigger tigger_name;
以下示例删除上面创建的触发器my_trigger-
ij> Drop trigger my_trigger; 0 rows inserted/updated/deleted
以下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); //获取Statement对象 Statement stmt=conn.createStatement(); //创建表结构 stmt.execute("CREATE TABLE Emp ( " + "Id INT NOT NULL, " + "Name VARCHAR(255), " + "Salary INT NOT NULL, " + "Location VARCHAR(255))"); //插入一些测试数据 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, 'Learnfk', 15000, 'Mumbai'), " + "(5, 'Trupthi', 45000, 'Kochin')"; stmt.execute(query); //创建备份表 stmt.execute("CREATE TABLE BackUp ( " + "Id INT NOT NULL, " + "Name VARCHAR(255), " + "Salary INT NOT NULL, " + "Location VARCHAR(255))"); //创建触发器 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"); //删除Name='Kalyan' or Name='Amit'的数据 stmt.executeUpdate("Delete From Emp where Name='Kalyan'"); stmt.executeUpdate("Delete From Emp where Name='Amit'"); //获取备份表的数据 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
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)