001/* 002 * Copyright (c) 2004-2023 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 * Purdue University 012 * Chapman Flack 013 */ 014package org.postgresql.pljava.example.annotation; 015 016import java.sql.Connection; 017import java.sql.DriverManager; 018import java.sql.ResultSet; 019import java.sql.SQLException; 020import java.sql.SQLIntegrityConstraintViolationException; 021import java.sql.Statement; 022 023import org.postgresql.pljava.TriggerData; 024import org.postgresql.pljava.annotation.Function; 025import org.postgresql.pljava.annotation.SQLAction; 026import org.postgresql.pljava.annotation.Trigger; 027import static org.postgresql.pljava.annotation.Trigger.Called.*; 028import static org.postgresql.pljava.annotation.Trigger.Constraint.*; 029import static org.postgresql.pljava.annotation.Trigger.Event.*; 030import static org.postgresql.pljava.annotation.Trigger.Scope.*; 031import static org.postgresql.pljava.annotation.Function.Security.*; 032 033import static org.postgresql.pljava.example.LoggerTest.logMessage; 034 035/** 036 * Example creating a couple of tables, and a function to be called when 037 * triggered by insertion into either table. In PostgreSQL 10 or later, 038 * also create a function and trigger that uses transition tables. 039 *<p> 040 * This example relies on {@code implementor} tags reflecting the PostgreSQL 041 * version, set up in the {@link ConditionalDDR} example. Transition tables 042 * appear in PG 10. 043 */ 044@SQLAction( 045 provides = "foobar tables", 046 install = { 047 "CREATE TABLE javatest.foobar_1 ( username text, stuff text )", 048 "CREATE TABLE javatest.foobar_2 ( username text, value numeric )" 049 }, 050 remove = { 051 "DROP TABLE javatest.foobar_2", 052 "DROP TABLE javatest.foobar_1" 053 } 054) 055@SQLAction( 056 requires = "constraint triggers", 057 install = "INSERT INTO javatest.foobar_2(value) VALUES (45)" 058) 059@SQLAction( 060 requires = "foobar triggers", 061 provides = "foobar2_42", 062 install = "INSERT INTO javatest.foobar_2(value) VALUES (42)" 063) 064@SQLAction( 065 requires = { "transition triggers", "foobar2_42" }, 066 install = "UPDATE javatest.foobar_2 SET value = 43 WHERE value = 42" 067) 068/* 069 * Note for another day: this would seem an excellent place to add a 070 * regression test for github issue #134 (make sure invocations of a 071 * trigger do not fail with SPI_ERROR_UNCONNECTED). However, any test 072 * here that runs from the deployment descriptor will be running when 073 * SPI is already connected, so a regression would not be caught. 074 * A proper test for it will have to wait for a proper testing harness 075 * invoking tests from outside PL/Java itself. 076 */ 077public class Triggers 078{ 079 /** 080 * insert user name in response to a trigger. 081 */ 082 @Function( 083 requires = "foobar tables", 084 provides = "foobar triggers", 085 schema = "javatest", 086 security = INVOKER, 087 triggers = { 088 @Trigger(called = BEFORE, table = "foobar_1", events = { INSERT } ), 089 @Trigger(called = BEFORE, scope = ROW, table = "foobar_2", 090 events = { INSERT } ) 091 }) 092 093 public static void insertUsername(TriggerData td) 094 throws SQLException 095 { 096 ResultSet nrs = td.getNew(); // expect NPE in a DELETE/STATEMENT trigger 097 String col2asString = nrs.getString(2); 098 if ( "43".equals(col2asString) ) 099 td.suppress(); 100 nrs.updateString( "username", "bob"); 101 } 102 103 /** 104 * Examine old and new rows in reponse to a trigger. 105 * Transition tables first became available in PostgreSQL 10. 106 */ 107 @Function( 108 implementor = "postgresql_ge_100000", 109 requires = "foobar tables", 110 provides = "transition triggers", 111 schema = "javatest", 112 security = INVOKER, 113 triggers = { 114 @Trigger(called = AFTER, table = "foobar_2", events = { UPDATE }, 115 tableOld = "oldrows", tableNew = "newrows" ) 116 }) 117 118 public static void examineRows(TriggerData td) 119 throws SQLException 120 { 121 Connection co = DriverManager.getConnection("jdbc:default:connection"); 122 Statement st = co.createStatement(); 123 ResultSet rs = st.executeQuery( 124 "SELECT o.value, n.value" + 125 " FROM oldrows o FULL JOIN newrows n USING (username)"); 126 rs.next(); 127 int oval = rs.getInt(1); 128 int nval = rs.getInt(2); 129 if ( 42 == oval && 43 == nval ) 130 logMessage( "INFO", "trigger transition table test ok"); 131 else 132 logMessage( "WARNING", String.format( 133 "trigger transition table oval %d nval %d", oval, nval)); 134 } 135 136 /** 137 * Throw exception if value to be inserted is 44. 138 */ 139 @Function( 140 requires = "foobar tables", 141 provides = "constraint triggers", 142 schema = "javatest", 143 security = INVOKER, 144 triggers = { 145 @Trigger(called = AFTER, table = "foobar_2", events = { INSERT }, 146 scope = ROW, constraint = NOT_DEFERRABLE ) 147 }) 148 149 public static void disallow44(TriggerData td) 150 throws SQLException 151 { 152 ResultSet nrs = td.getNew(); 153 if ( 44 == nrs.getInt( "value") ) 154 throw new SQLIntegrityConstraintViolationException( 155 "44 shall not be inserted", "23000"); 156 } 157}