New answers tagged

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... ...


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.) ...


0

If you use BEFORE INSERT instead of AFTER INSERT in the update_referral trigger, you might be able to avoid the loop. You could directly update chezmoi_user_details inside update_referral instructions, all in one trigger. You can find explanations here: http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html


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 @> ...


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 ...


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 ...


0

Ok, after some research, here's the answers I found. Q1: Is it possible to detect the user that executed the query in MySQL? It is possible to detect with which user by using: select user(); http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user Beware: In triggers and events current_user() returns the user who defined the object ...


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 ...


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 ...


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 ...


0

I addressed this with the PL/SQL project manager years ago, and was told that it is not possible. You can't address the :NEW or :OLD columns as a record and convert them to anything. You can create a temporary table that is either identical to the table that you are writing the trigger for or generic, i.e. table_name, column_name, column_type, clob. You ...


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 ...


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

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 ...


0

You dynamically build the query. Use CONCAT to construct the SQL to include the column name provided; PREPARE it`; EXECUTE it; DEALLOCATE


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(); '''))


0

According to your question the sample tables are follows: Table:ITEMS Field's Name Type ------------ ------ Serialno Number (PK) Qty Number Table:ORDERS Field's Name Type ------------ ------ Orderno Number (PK) Serialno Number (PK)(FK) Qty Number The following trigger ...


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 ...


-1

No trigger is needed. If you SELECT from Stockitem the DBMS will take a shared lock on that table. This will prevent updates to this table since UPDATEs need an exclusive lock, which cannot be granted while the shared lock exists. So, in the application (or stored procedure or whatever) start a transaction (to ensure locks are held until we're finished) ...


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, ...


0

Triggers are procedural, so my inclination would be to perform the join and update as separate steps; this IMHO would make for simpler and more readable DML.This code should do what you want: DELIMITER $$ CREATE TRIGGER owed AFTER UPDATE ON bookings FOR EACH ROW BEGIN DECLARE dept VARCHAR(20); DECLARE rate DECIMAL(3, 2); IF ...


0

You need to have DELIMITER directives both before and after the CREATE TRIGGER statement, you need to end individual simple statements with semicolons, and you need to end the complete CREATE TRIGGER with the delimiter specified in the first DELIMITER directive: DELIMITER // CREATE TRIGGER subtract_quantity AFTER INSERT ON parts_used FOR EACH ROW BEGIN ...



Top 50 recent answers are included