Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

4
votes
1answer
89 views

Finding changed column values

Table 1 ID |COL2|COL3|COL4|COL5| ROW1| 1 | 1 | 1 | 1 | ROW2| 1 | 3 | 2 | 6 | ROW3| 1 | 1 | 1 | 1 | ROW4| 1 | 1 | 1 | 1 | ROW5| 1 | 1 | 1 | 1 | ROW6| 1 | 1 | 1 | 1 | Problem ...
0
votes
0answers
44 views

Query hung or way too long?

As part of the proccess of new items added to our system, some tables and relations are created programmaticaly via trigger, but it seems to be failing quite often right now. The DDL seems to be ...
0
votes
2answers
104 views

Can a trigger catch updates done by a select for update + lob.write()?

It's maybe a stupid question, but I didn't find an explicit answer... I want to catch any update on my oracle db, so I want to use triggers, but the triggers not been activated when using SELECT FOR ...
1
vote
4answers
59 views

User privileges error on server maria db Create trigger Drop trigger

User privileges error on server maria db User : user1 DB : db1 Error 15:00:46 DROP TRIGGER IF EXISTS audit_trigger Error Code: 1419. You do not have the SUPER privilege and binary logging ...
4
votes
1answer
101 views

How to use COLUMNS_UPDATED to check if any of certain columns are updated?

I have table with 42 columns and a trigger which should do some stuff when 38 of these columns are updated. So, I need to skip the logic if the rest 4 columns are changed. I can use UPDATE() function ...
6
votes
2answers
158 views

SQL Server using index scan instead of seek when updating a view that has an INSTEAD OF UPDATE trigger via a cursor

I have a 3rd party application which uses a cursor that updates rows in my SQL Server 2014 table. I am unable to modify this code and have therefore devised a scheme to point the cursor to a view ...
3
votes
0answers
30 views

Should I do the maths within PostgreSQL for ACIDity?

It is my first question on Database Administration Stack Exchange. Please be kind if something is not done the nice way.. I would like to be advised concerning the way I'm working on my database, in ...
0
votes
1answer
60 views

Set Field To Have Default Value Of Another

I am trying to create a trigger using the below syntax, however SSMS keeps barking at me with the below error. Msg 208, Level 16, State 4, Procedure test1, Line 1 Invalid object name 'data123'. ...
0
votes
1answer
18 views

InnoDB, Writing TRIGGER to INSERT into tables

Table1 "user_address" Field "u_id" Field "street_name" Field "locality_name" Table2 "street_master" Field "s_id" Field "s_name" I am supposed to write a trigger which BEFORE INSERT ON ...
-2
votes
1answer
55 views

Trigger is not working in SQL Server 2012 on runtime? [closed]

In SQL Server 2012, I create two tables TABLE1 and TABLE2 After that, I created a trigger for TABLE1 Trigger name: PUSH_TABLE2 After creating the trigger PUSH_TABLE2, I test with below insert ...
2
votes
2answers
33 views

MySQL Trigger Query Not Deleting As Expected

I have a test database as shown below. If date is updated in tbl1, I would like for a trigger to fire that removes all entries from tbl2 where tbl1.host = tbl2.host2 AND tbl1.date = NEW.date. This is ...
2
votes
1answer
78 views

Inserting data into table in server B whenever table in server A gets updated

I want to synchronize data from table 1 in server A to tables 2 and 3 in server B, whenever table 1 gets updated. Tables from server A are not related to tables from server B, so I'd have to specify ...
1
vote
1answer
111 views

SQL Server Trigger does not update all table records

This is my first question so please go easy on me. We are using Access 2013 as a front-end and SQL Server 2012 with MS SQL Server Management Studio 11.0.2100.60 backend with ODBC connections. I am ...
1
vote
1answer
67 views

Trigger Not Updating

Do I have an issue with my syntax? I created this trigger as sometimes the sales personnel forget to input the sale date (I Know it should be automatically captured which is an update in progress) ...
1
vote
1answer
98 views

Can I emulate Oracle's autonomous transaction in MySQL?

I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any ...
1
vote
1answer
72 views

How to get value of last updated row from a table?

I want to insert the last updated row into a new table using a trigger? How to fetch that row if any of the column (value) from that table has been updated or changed? I am using this code: CREATE ...
0
votes
1answer
60 views

postgresql custom TEXT SEARCH CONFIGURATION with tsvector_update_trigger

I would like to use a custom text search configuration with tsvector_update_trigger to be able to update tsv field with french dictionary and unaccent. So I did that : CREATE TEXT SEARCH ...
0
votes
0answers
29 views

How to replicate SQL Server rowversion behaviour for some fields only

SQL Server version 10.50.4000. I have a table: create table people ( national_id char(16), full_name char(255), last_visited datetime ) I want to add a field that will work exactly ...
0
votes
0answers
18 views

Is automatic disabling of DML triggers possible during SSDT publish?

During ssdt deployment I am concerned about having DML triggers in place on tables that may slow down performance of table recreation a lot. We are facing the issue that the column order in the ...
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
84 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
35 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
22 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
263 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
57 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
121 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
51 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
48 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
45 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
45 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
26 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
154 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. ...
1
vote
2answers
74 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
163 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
44 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
89 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
183 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
34 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
60 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
49 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
83 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
139 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
98 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
41 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
103 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
28 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
992 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?