Hot answers tagged

7

You can consider a rule as transforming the command being executed, whereas a trigger is altering the data itself. (Note that this is a simplification! Spend some time reading the documentation for CREATE TRIGGER and CREATE RULE rather than trusting some random internet guy.) So you can define a rule that is invoked when PostgreSQL sees a certain command, ...


5

The right way to do this will probably be to have your application INSERT via a stored procedure (or in Postgres a function). If stored procedures are not an option, create a view and rewrite inserts against that view so that they affect some other table. CREATE TABLE t ( a integer, b integer ); CREATE VIEW v AS SELECT t.a, t.b FROM t; CREATE ...


4

The session information function pg_trigger_depth() would solve your problem with trigger recursion. Requires Postgres 9.2 or later. The manual: current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger) Best used it in a WHEN clause to CREATE TRIGGER: CREATE OR REPLACE FUNCTION ...


4

You can specify the first and/or last trigger to fire using sys.sp_settriggerorder. If you have two or three triggers, this allows you to completely control the execution order of all triggers. If you have more than three, you can make one first, another fire last, but the order of execution for the triggers in the middle will be non-deterministic. In your ...


4

Problem You had to pick the spot where all possible complications come together. SQL (or PL/pgSQL) does not allow to parameterize identifiers. You need dynamic SQL with EXECUTE for that. But the special plpgsql variable NEW in trigger functions is not visible inside dynamic code executed with EXECUTE. And it's further complicated by passing column names ...


3

One option would be to: Jack up the security on the table so normal users can't directly access it at all, and remove the protecting trigger. Create an updatable view on the table which is accessible by normal users, and add an INSTEAD OF trigger to protect the column from updates (assuming you want it still visible; if not, simply omit it completely from ...


3

@Chris provides an excellent answer for the question in the title. Your question itself is inconsistent in multiple places, talking about a trigger "after delete", but showing a trigger AFTER INSERT. Rule and trigger are on different tables and your trigger function is needlessly convoluted. A clean way could look like this: CREATE OR REPLACE FUNCTION ...


2

Depending on what you are trying to achieve with this, you may be out of luck. First of all, names cannot be parametrised in MySQL otherwise than using a prepared statement. – So, can we use a prepared statement here? No. Prepared statements are not allowed in triggers, according to the manual: SQL syntax for prepared statements can be used within ...


2

You can only make persistent changes to NEW in a before row trigger. An after trigger doesn't fire until the record has already been written to the table. To change the data that will be written to the table, you must use a before row trigger. Changing NEW in an after row trigger is possible, but pointless, since the contents of NEW get written to the table ...


2

Your first link (Why is truncate DDL?) is about Oracle, not MySQL. While relevant, different DBMS implement same features differently. In Oracle and SQL Server TRUNCATE is DDL, not DML. In MySQL, it matter which version you use: Until version 5.1, the documentation says: Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all ...


2

If you don't specify FOR EACH ROW in your CREATE TRIGGER statement, it will default to FOR EACH STATEMENT. In this case, the OLD and NEW records will never be assigned - in the end, which row should they refer to, if you change, for example, a hundred of them? So, create your trigger as follows: CREATE TRIGGER TR1 AFTER DELETE ON ...


2

flag will be what it had been before the UPDATE started. Maybe it would feel 'right' to use OLD.flag instead of NEW.flag, at least in the if? Addenda NEW.flag contains the new value. NEW.flag <=> OLD.flag checks to see if it changed. I doubt if there is any way to see if it was set but not changed. (Note that I used the "NULL-safe equal" operator.) ...


1

You (as in, the user) won't generally sort data within a table. You (as in, a user) will sort the output from queries. The order that the data is stored is generally irrelevant to you, and the database will store the data in a way that makes sense for it. If you want/need the data ordered when you run a query, put the order by statement in the query... ...


1

createIndex := 'CREATE INDEX idx_' || Fulltable_name || '_properties_crosspr_gist ON public.' || Fulltable_name || ' USING gist (properties) WHERE properties @> $$"block_level_0"=>"cerber-head"$$::hstore AND properties @> $$"block_level_1"=>"head"$$::hstore AND properties @> ...


1

I implemented what you are trying to do this way: the transformation to a user defined object happens in a packaged procedure that is called by triggers. ---all comments and debugging removed to keep it simple---- CREATE OR REPLACE TRIGGER when_close_ini AFTER UPDATE OF end_date ON YOUR_TABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW ...


1

The issue is that Postgres requires you to include the braces at the end of the function name when calling DROP FUNCTION. If you change your code to the following, it'll work: def downgrade(): conn = op.get_bind() conn.execute(sa.sql.text(''' DROP TRIGGER update_rated_trigger ON rating; DROP FUNCTION update_rated(); '''))



Only top voted, non community-wiki answers of a minimum length are eligible