Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
0answers
30 views

Backing up of database

I have a live MySQL DB and a Back up DB. What I would like to do is, once any changes are made to the live DB, the back up DB need to be immediately updated. I had thought about Triggers but the DB ...
1
vote
1answer
113 views

After Update/Insert trigger sometimes fails to do its job

I've got this trigger: CREATE OR REPLACE TRIGGER "DOKCENTRA"."INTDOC_AFTER" AFTER UPDATE OR INSERT ON "DOKCENTRA"."INT_DOC" FOR EACH ROW BEGIN IF UPDATING THEN IF ( :NEW.DOCNUMBER IS ...
0
votes
0answers
37 views

What is the Substitute of ALTER TRIGGER and CREATE or REPLACE TRIGGER in MySQL

I want to insert a new Question ID using trigger in MySQL table when I insert a new question in MySQL table from JSP page. Using Oracle this code will do the same thing but I don't know how to do this ...
0
votes
0answers
23 views

Is there a way to automatically set triggers to update a field that is computed as an aggregation of other fields?

Let's say that I have these tables: user, task, and submission. A user can create a submission which will be scored by the system, which will assign a value to submission.score. Now, everytime a ...
3
votes
2answers
76 views

Edit Application Name to Log Connection once for Logons on Pooled Connections

I am wondering if I can change the application name of a connection in SQL Server itself, e.g. in the logon trigger? Is that possible or is the application name "read only"? Edit to explain the ...
6
votes
1answer
281 views

Logon Trigger preventing any connection

I was testing a logon trigger to restrict connections to certain IPs. It looks like the trigger was not correct. Now I can't log in to the server even as SA. Is there a way to fix this?
4
votes
1answer
66 views

“TRUNCATE doesn't run ON DELETE trigger” true or false

I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here Why is truncate DDL? I this answer I found a sentence as given below "The fact ...
1
vote
1answer
152 views

Create MySQL trigger that updates position of each row when sorted by a specific field

Let's see an example, I've got this table that contains teams, each of them having a certain number of points: CREATE TABLE teams ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, ...
0
votes
0answers
55 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
61 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 ...
3
votes
1answer
55 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 ...
1
vote
1answer
47 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 ...
0
votes
0answers
29 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 ...
5
votes
2answers
155 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. I'...
1
vote
2answers
78 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 ...
3
votes
1answer
296 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
58 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
1answer
111 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 ...
0
votes
1answer
26 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
316 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 ;; ...
0
votes
0answers
45 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
72 views

Creating index using dynamic SQL [closed]

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 @> '"...
0
votes
1answer
61 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
0answers
15 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( ...
3
votes
1answer
88 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 ...
0
votes
2answers
171 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 ...
5
votes
2answers
100 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 ...
0
votes
1answer
44 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
144 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
33 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 ...
8
votes
3answers
2k 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
40 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
80 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
23 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 (...
0
votes
1answer
143 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 ...
0
votes
0answers
56 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 ...
0
votes
0answers
20 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 information_schema....
1
vote
0answers
26 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 ...
0
votes
1answer
16 views

First Insert is skipped by the trigger

I am facing a very strange issue as i have implemented a trigger on tableA which does encrypt data and insert data to another tableB and update column to 4 digit. The issue is First insert is somehow ...
3
votes
1answer
121 views

Enable MySQL trigger for all users in mysql

I have setup triggers in my MySQL tables and when I applied them they were restricted to the account I used when creating the triggers, now I have multiple users in my MySQL and I want it so that the ...
0
votes
1answer
36 views

syntax error on mathematical operation postgresql

I'm trying to declare this function to be used as a trigger: create or replace function atualiza_qtfunc() returns trigger as $f_at_qtfunc$ --f_at_func = função atualiza quantidade funcionários ...
0
votes
1answer
75 views

MySQL (mariaDB) trigger syntax always wrong

Im making a small game and i need to have a trigger on my members table, which will check the experience column, and anytime it gets bigger than certain values, the 'level' will become level+1. This ...
0
votes
1answer
37 views

MySQL conditional trigger delete

I have a few tables (6 to be exact) and I want to make a trigger that will delete a row from a table based on a value from the main table. To elaborate, Let's say I have a main table called "main" ...
0
votes
1answer
64 views

Unable to insert into table after creating an After insert trigger

I have two tables Table one: default_sellers_commissions have: id value - INT Table two: marketplace_saleperpartner have: id userID commission - INT default '0.00' commission_id what I need ...
0
votes
0answers
22 views

MySQL Trigger at Insertion with nested CAST

So I'm using MySQL 5.5 and trying to insert a VARCHAR as ID (since my teacher requested so), I am trying that each time there is an insertion of a new USER, a trigger automatically assigns an ID to it....
0
votes
1answer
25 views

postgresql trigger unexpected behaviour by many copies of inserted row

On insert in tbl1, the trigger I am working on, copies rows in tbl2 with some updates. Trigger: create or replace function myprocedure() returns trigger as begin IF (TG_OP = 'INSERT')THEN insert ...
0
votes
1answer
113 views

MySQL - Force replication to statement for INSERT to a table to fire trigger on slave

We have a PROD DB which replicates into a slave DB using mixed replication. We want to add a trigger so that a row is added to our DW when a row is INSERTed into table_a (on master). The issue is that ...
1
vote
2answers
43 views

Triggers hidden

I had to edit a trigger in my MySQL database. But when typing SHOW TRIGGERS nothing appear. My database is the only one selected but I also tried SHOW TRIGGERS FROM myDBname. Still nothing. This ...
1
vote
0answers
57 views

How to see text of the query which triggers the trigger inside trigger?

I have very small database with a bit complex triggers which works very slow. Those triggers are written to extend business logic (becase we can not manage the code of the application we are using). ...
1
vote
1answer
136 views

Get triggers/table names in PostgreSQL

How can I get a listing of all triggers for a database along w/ the table that it belongs to? I assume that I need pg_trigger and pg_tables but I'm not quite sure how to join them together.