001/* 002 * Copyright (c) 2004-2019 Tada AB and other contributors, as listed below. 003 * 004 * All rights reserved. This program and the accompanying materials 005 * are made available under the terms of the The BSD 3-Clause License 006 * which accompanies this distribution, and is available at 007 * http://opensource.org/licenses/BSD-3-Clause 008 * 009 * Contributors: 010 * Tada AB 011 * Chapman Flack 012 */ 013package org.postgresql.pljava.example; 014 015import java.sql.Connection; 016import java.sql.DriverManager; 017import java.sql.PreparedStatement; 018import java.sql.ResultSet; 019import java.sql.SQLException; 020import java.sql.Timestamp; 021import java.util.logging.Logger; 022 023import org.postgresql.pljava.SessionManager; 024import org.postgresql.pljava.TriggerData; 025import org.postgresql.pljava.TriggerException; 026 027/** 028 * This class contains some triggers that I found written in C under the 029 * contrib/spi directory of the postgres source distribution. Code to create the 030 * necessary tables, functions, triggers, and some code to actually execute them 031 * can be found in class {@code org.postgresql.pljava.test.Tester} (no longer 032 * part of the build, see under {@code src/java/test}). 033 * 034 * @author Thomas Hallgren 035 */ 036public class Triggers { 037 public static void afterUsernameInsert(TriggerData td) throws SQLException { 038 Logger log = Logger.getAnonymousLogger(); 039 log.info("After username insert, username = " 040 + td.getNew().getInt("username")); 041 } 042 043 public static void afterUsernameUpdate(TriggerData td) throws SQLException { 044 Logger log = Logger.getAnonymousLogger(); 045 if (td.isFiredForStatement()) 046 throw new TriggerException(td, "can't process STATEMENT events"); 047 048 if (td.isFiredBefore()) 049 throw new TriggerException(td, "must be fired after event"); 050 051 if (!td.isFiredByUpdate()) 052 throw new TriggerException(td, 053 "can't process DELETE or INSERT events"); 054 055 ResultSet _new = td.getNew(); 056 String[] args = td.getArguments(); 057 if (args.length != 1) 058 throw new TriggerException(td, "one argument was expected"); 059 String colName = args[0]; 060 061 ResultSet _old = td.getOld(); 062 log.info("Old name is \"" + _old.getString(colName) + '"'); 063 log.info("New name is \"" + _new.getString(colName) + '"'); 064 } 065 066 /** 067 * insert user name in response to a trigger. 068 */ 069 public static void insertUsername(TriggerData td) throws SQLException { 070 if (td.isFiredForStatement()) 071 throw new TriggerException(td, "can't process STATEMENT events"); 072 073 if (td.isFiredAfter()) 074 throw new TriggerException(td, "must be fired before event"); 075 076 if (td.isFiredByDelete()) 077 throw new TriggerException(td, "can't process DELETE events"); 078 079 ResultSet _new = td.getNew(); 080 String[] args = td.getArguments(); 081 if (args.length != 1) 082 throw new TriggerException(td, "one argument was expected"); 083 084 if (_new.getString(args[0]) == null) 085 _new.updateString(args[0], SessionManager.current().getUserName()); 086 } 087 088 public static void leakStatements(TriggerData td) throws SQLException { 089 StringBuffer buf = new StringBuffer(); 090 091 buf.append("Trigger "); 092 buf.append(td.getName()); 093 buf.append(" declared on table "); 094 buf.append(td.getTableName()); 095 buf.append(" was fired "); 096 if (td.isFiredAfter()) 097 buf.append("after"); 098 else 099 buf.append("before"); 100 101 buf.append(' '); 102 if (td.isFiredByDelete()) 103 buf.append("delete"); 104 else if (td.isFiredByInsert()) 105 buf.append("insert"); 106 else 107 buf.append("update"); 108 109 if (td.isFiredForEachRow()) 110 buf.append(" on each row"); 111 112 // DON'T DO LIKE THIS!!! Connection, PreparedStatement, and ResultSet 113 // instances 114 // should always be closed. 115 // 116 int max = Integer.MIN_VALUE; 117 Connection conn = DriverManager 118 .getConnection("jdbc:default:connection"); 119 PreparedStatement stmt = conn 120 .prepareStatement("SELECT base FROM setReturnExample(?, ?)"); 121 stmt.setInt(1, 5); 122 stmt.setInt(2, 8); 123 ResultSet rs = stmt.executeQuery(); 124 while (rs.next()) { 125 int base = rs.getInt(1); 126 if (base > max) 127 max = base; 128 } 129 buf.append(" reports max = " + max); 130 stmt = conn 131 .prepareStatement("INSERT INTO javatest.mdt (idesc) VALUES (?)"); 132 stmt.setString(1, buf.toString()); 133 stmt.executeUpdate(); 134 } 135 136 /** 137 * Update a modification time when the row is updated. 138 */ 139 public static void moddatetime(TriggerData td) throws SQLException { 140 if (td.isFiredForStatement()) 141 throw new TriggerException(td, "can't process STATEMENT events"); 142 143 if (td.isFiredAfter()) 144 throw new TriggerException(td, "must be fired before event"); 145 146 if (!td.isFiredByUpdate()) 147 throw new TriggerException(td, "can only process UPDATE events"); 148 149 ResultSet _new = td.getNew(); 150 String[] args = td.getArguments(); 151 if (args.length != 1) 152 throw new TriggerException(td, "one argument was expected"); 153 _new.updateTimestamp(args[0], new Timestamp(System.currentTimeMillis())); 154 } 155}