Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

1
vote
1answer
7 views

Create MySQL trigger that updates all rows sorted by a field

How can I create a trigger that updates all rows that are sorted in a certain order? Let's see an example, I've got this table that contains teams, each of them having a certain number of points: ...
0
votes
0answers
25 views

How to add a trigger on table create in Mysql

I want to apply a trigger on all tables if my database. I already created a script in PHP which will add triggers in all existing tables. But if any new tables are added in database, I have to run ...
4
votes
1answer
16 views

Checking for fields not on SET clause on before update trigger on MySQL

I want to set a before update trigger that sets a column to a given value if an update query is done and that column has no new value on the set clause. The table I'm using. (Sort of) DROP TABLE IF ...
4
votes
2answers
3k views

Trigger in MySQL to prevent insertion

I want to create the trigger which prevents insertion in the reservation table of flights (numflights) which does not exist. Tables ---------------------------------------------- flights(numflight, ...
2
votes
1answer
111 views

MySQL stored procedure updates twice

I have this stored procedure: DELIMITER $$ CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT) BEGIN DECLARE ...
0
votes
1answer
34 views

can't update table in stored function/trigger because [on hold]

I have referred to many questions, like here and also created a question on SO(No response there), but am not able to understand why it's happening in my case as am not even doing anything in the ...
0
votes
1answer
288 views

Update query in a trigger

I have a configuration table: Table: refdatecol - id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
0
votes
1answer
45 views

Creating index using dynamic SQL [on hold]

I have following code for creating gist index CREATE INDEX web_stats_project_107_properties_crosspromo_gist_idx ON web_stats_project_107 USING gist (properties) WHERE properties @> ...
1
vote
1answer
33 views

Trigger for removing group with no users left in

I have three tables: USERS, GROUPS, and GROUP_MEMBERSHIP CREATE TABLE USERS ( ID BIGSERIAL NOT NULL, NICKNAME VARCHAR(20) NOT NULL constraint USER_EXISTS UNIQUE, PRIMARY KEY (ID) ); CREATE TABLE ...
3
votes
1answer
28 views

Set the Priority for two different “after insert” triggers in SQL Server

I'm trying to create triggers by functionality,instead of DML operation. For exp,I have 2 triggers A and B. A is for "AFTER insert, update , DELETE" , and B is for "AFTER INSERT". how can I know ...
0
votes
0answers
14 views

MySQL Trigger Replication between Master and Slave

we are running a rather highly loaded MySQL Application replicating between Master and two Slave instances. We are using Statement-based replication. Recently, we have upgraded our hardware and a ...
0
votes
1answer
392 views

Oracle trigger error: table %s.%s is mutating, trigger/function may not see it

I am having some issues while trying to use triggers over tables where I need to get the reference of custom data type from a table. I can't seem to get it to work, here are the scripts I am using ...
7
votes
1answer
654 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
5
votes
2answers
149 views

Trigger to create symmetrical rows

I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. ...
3
votes
1answer
76 views

mysql custom query logging for inter-software data exchange

Some 2-3 years I had written some triggers (one per table) in SQL server allowing me to log queries in our main software. ALTER TRIGGER [dbo].[SUIVI_REQUETE_CAT_CATBRUT] ON [dbo].[CAT_CATBRUT] AFTER ...
1
vote
2answers
53 views

Serialize :new in trigger

I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new. What I am looking for ...
-1
votes
0answers
63 views

How to run an exe from an oracle trigger

I am new to Oracle came from SQL Background. I want to run an exe using trigger in oracle. As there is no direct way to do this, I have created a java procedure to run a trigger. my code is as ...
0
votes
0answers
34 views

database constraint using trigger [closed]

I have modified my question as follows. I have created 3 tables as follows. create table s ( sno char(5), sname varchar(25), status int, city varchar(25), primary key(sno)); create table p ...
3
votes
1answer
50 views

Trigger function taking column names as parameters to modify the row

I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet): CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) RETURNS trigger AS ...
1
vote
1answer
26 views

Disable/enable PostgreSQL trigger inside function for the same table

I have a trigger that protects updating some column. I also have a trigger that should update that same column after insert on that table. I've used triggers like this and it works if I need to update ...
2
votes
0answers
47 views

mysql trigger on a federated table

I have two MySQL(5.6) databases D1 and D2 on two different machines. There is a table T1 on D1 and there are two tables T2A and T2B on D2. I want to update T2B whenever T1 is updated. T2A is a ...
5
votes
2answers
1k views

Federated tables and triggers

This is the scenario. Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2). I have a table (T2) on DB2 that needs to "fetch" rows from another table (T1) on DB1. ...
2
votes
1answer
115 views

SQL Server 2008 Triggers and Merge Replication

I am trying to have a trigger fire when the Merge agent inserts or deletes data into / from a table. It is supposed to insert data into a report table for use with SSRS. I DO NOT have the "NOT FOR ...
0
votes
1answer
21 views

MYSQL after update trigger

I am new to database trigger. I really have no idea why i cant update the Label if the Quantity more than equal to 1000 CREATE TRIGGER TRG_UPDATE_STATUS AFTER UPDATE ON product FOR EACH ROW BEGIN ...
2
votes
2answers
37 views

Set variable with dynamic name in trigger (MySQL)

I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this: delimiter ;; ...
2
votes
1answer
667 views

SQL Server: update trigger fires before insert trigger

I have an application with a SQL Server-hosted database. I do not have access to the application code, but I do have full access to the database. I have added my own custom auditing to a table to ...
0
votes
0answers
20 views

mysql returns FUNCTION .LAST_INSERT_ID does not exist

I'm new to MySQL. I have created two tables. create table Shopping( Trans_ID int not null auto_increment primary key, date date, item varchar, quantity double, rate ...
0
votes
1answer
29 views

Postgresql cannot drop trigger [closed]

I'm running the following with alembic: def downgrade(): conn = op.get_bind() conn.execute(sa.sql.text(''' DROP TRIGGER update_rated_trigger ON rating; DROP FUNCTION update_rated; ''')) ...
0
votes
1answer
970 views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
0
votes
0answers
11 views

Relationship based access control (ReBAC)

In my application, access control is based on relationships between entities. Consider this simplified schema: CREATE TABLE user (...); CREATE TABLE group (...); CREATE TABLE group_members( ...
0
votes
2answers
91 views

Trigger to compare inserted value with value from another table

I have two tables: Stockitem (serialno, qty) serialno is the primary key and Order(serialno,orderno,qty) orderno and serialno are the primary keys. I want to create a trigger in order to ensure ...
6
votes
2answers
87 views

Which executes first? Trigger or rule?

Using PostgreSQL. Had a trigger which does something after delete and made a rule which stopped delete from happening if something will be true. My question is: will the rule prevent the trigger ...
1
vote
1answer
125 views

MySQL Local Variable Scope and Triggers

I have a couple triggers that automatically update invoice data and set some statuses in the database. However, it appears that if 2 Triggers have variables that are named the same, they get ...
0
votes
1answer
31 views

MySQL Trigger to Update a Row in a Separate Table via Joins if Only One Row is Updated

I am trying to create a trigger so that when a column in a table called bookings is updated it will update a a calculated price value in table departments. I currently have my tables set up as so ...
0
votes
1answer
46 views

MySQL Using a trigger to update quantity on another table if part is used

I am new to triggers but want to know how I can use one in my database. I have created a table for a maintenance log, a parts inventory, and a parts_used junction table. When maintenance is carried ...
0
votes
0answers
22 views

Postgres: Recreate TRIGGER after replacing function?

Given a function like this: CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$ BEGIN -- do something fancy END; $$ LANGUAGE plpgsql; which is used in a TRIGGER: CREATE TRIGGER bar ...
0
votes
2answers
176 views

Logging the Execution times of dynamically constructed mysql stored procedures

I have a MySQL Server. The MySQL server contains a huge network data collection. The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
3
votes
3answers
4k views

Oracle - abort within a “before insert” trigger without throwing an exception

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...
0
votes
2answers
305 views

how to use trigger to make SQL Job's step success or fail

Step 1. Call a script to trigger application process. When the application process start, it will insert a record to a specific table. And once the process ends, that record will be deleted from ...
9
votes
3answers
513 views

How can I drop all triggers in a single database?

I have a database with 104 triggers, is there a way to delete all the triggers with a single command from a single database called 'system_db_audits?
1
vote
2answers
35 views

Update audit database name in triggers and views of main database?

I have a a system with two databases, main database and audit database. A lot of the triggers and table views in the main database and audit database are referencing from one database to the other. No ...
1
vote
1answer
69 views

SQL current timestamp

I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if ...
0
votes
0answers
16 views

Wordpress MySQL - Prevent Duplicates

Hello, I am having troubles with my Trigger, I need another pair of eyes to figure out where I am going wrong. Basically all I am trying to do is to prevent wordpress from inserting duplicate posts ...
1
vote
1answer
148 views

Trigger on a Schema

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one. --Database level trigger. ...
1
vote
1answer
74 views

Simple trigger to update two columns in one table

I want to create a trigger MySQL that updates two fields at the same time. Whenever a record is added or inserted, I want the contents to be copied to one field or the other, depending on which one ...
2
votes
2answers
309 views

Java Spring application Database triggers auditing - how to provide the right user that made the change

We are developing a new web application. one of the most basic requirement is to audit all entities changes into a separate table. We would like to use DB triggers for that purpose. We use MySQL as ...
0
votes
0answers
18 views

Syncing DB's between local and live server?

I need to sync two tables between MySQL databases. I have read about MySql Replication. But not sure whether it can be used in this scenario. What I want to achieve is explained below. There are two ...
1
vote
1answer
3k views

The user specified as a definer ('root'@'%') does not exist

I have a trigger defined as follow: USE `veeva_bi`; DELIMITER $$ DROP TRIGGER IF EXISTS veeva_bi.account_ai$$ USE `veeva_bi`$$ CREATE DEFINER = CURRENT_USER TRIGGER `veeva_bi`.`account_ai` AFTER ...
0
votes
0answers
10 views

Fire trigger when Routine was created in MySQL

I try to track changes of Stored Procedures and UDFs in MySQL. That means, if a SP or UDF is created, I want to copy the information into another table. I tried to clone the ...
1
vote
0answers
15 views

Mysql trigger with group_concat

I'm trying to create insert and update triggers with SELECT GROUP_CONCAT request on one of the fields; This select query perfectly works, when I trying it in console, but trigger saves only first ...