Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

8
votes
3answers
1k 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
39 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
77 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
22 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
126 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
38 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
16 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
22 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
93 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
35 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
56 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
33 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
49 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
20 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 ...
0
votes
1answer
24 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
95 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
35 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
54 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
88 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.
0
votes
0answers
17 views

How do I maintain a separate table for precomputed results?

Suppose that I have the following (fictitious) schema: CREATE TABLE projects (..., client TEXT NOT NULL, ...); CREATE TABLE client_names (name TEXT NOT NULL PRIMARY KEY, soundex TEXT NOT NULL); ...
0
votes
1answer
200 views

Syntax error with concat in trigger near signal statement

Please help me with syntax for MySQL 5.6. For some reason I get the error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ...
0
votes
2answers
249 views

Trick for AUTO_INCREMENT on composite keys in InnoDB

In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as AUTO_INCREMENT. For example consider the following table: CREATE ...
0
votes
0answers
57 views

MySQL procedure with sys_eval (mysqludf) called by trigger

I'm executing a trigger after a row in a certain table has been updated. That trigger calls another procedure which is supposed to do some quick checks before executing a small pythonscript. If the ...
0
votes
1answer
28 views

How to log login attempts with machine name

Is there a way to log login attempts in SQL Server, including machine name together with the machine IP address? I read something about login triggers, but I don't know if it's useful for my needs.
2
votes
2answers
173 views

DDL trigger permission Error

I have a two users, user1 and user2. User1 has sysadmin rights and I have created a database level trigger through sa. Now I am trying to create a table through user2 who has db-ddladmin rights and ...
1
vote
1answer
30 views

MySQL Trigger Error [closed]

I receive the following error when below MySQL statements are executed: 1064 - You have an error in your SQL syntax; Can anyone explain the error ? CREATE TRIGGER `before_quiz_attempt_delete` ...
3
votes
1answer
245 views

Is Service Broker the best choice for auditing data changes on SQL Server Express?

My project is to audit 5 to 10 existing tables in our system without prolonging transactions. Whatever method is used, it has to work on SQL Server Express 2005 to (eventually) 2016. I've done ...
3
votes
1answer
138 views

Trigger actions on database creation or attach

On our dev environment we need to run a bunch of actions when some databases are created or attached. Actions like cleaning up some tables, reseeding some others, changing emails, etc. I was trying to ...
0
votes
1answer
144 views

MySQL trigger if new value is not equal to varchar

StackExchange, I'm trying to create a trigger in MySQL (before insert) that gives an error if the new value of "sifra" is not S, P, K, A or Z, but it I'm getting an error even if the said criteria is ...
1
vote
1answer
92 views

Postgresql INSERT triggers “0 row affected” workaround

In a INSERT trigger based table partition, insert always give "0 row affected". I think this problem is well know, it is even in the todo. Is there any workarounds for this issue in 9.4?
4
votes
1answer
691 views

triggers - using the inserted / deleted tables in Dynamic SQL

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables. Declare @NewID varchar(50) = ...
0
votes
1answer
58 views

Update one mysql column when another is edited

I'm trying to make a datetime field that automatically gets updated with the current time only if there was a change to a certain field. It seems I have a syntax error. I try to have ...
2
votes
2answers
156 views

Trigger to create a variable Table to be sent to service broker

I've setup a service broker for the first time and tested it... Looks good. Everytime a DML operation happens on a specific table; Let's say the Price Table, a trigger calls a stored procedure passing ...
-1
votes
1answer
20 views

cannot consistently view or drop a trigger

I have a database trigger called ddl_refresh_views that provides dynamic binding for views (if you alter view V, it refreshes all the views dependent on V, and all the views dependent on those views ...
0
votes
0answers
98 views

Creating audit triggers in a generic way for mysql tables

We have many many tables in our operational database. We would like to create an audit table for each one of them. We would like to create a trigger for each of our tables that will simply insert an ...
0
votes
0answers
15 views

Automatic creation of MySQL triggers

I would like to automatically create triggers for several table as part of the deployment process. trigger example: CREATE DEFINER=`root`@`localhost` TRIGGER xxx_admin.payment_AINS AFTER INSERT ON ...
1
vote
1answer
47 views

Trigger in MySQL upon changing stock status

This are one of the requirement that have to be done for my assignment and I am having a trouble on how or where do i start. my lecturer told us smth having a trigger and he is reluctant to guide us ...
0
votes
1answer
46 views

RAISERROR on a trigger. What will happen? Will we get informed?

What will happen if a trigger on a table hits the part of the trigger that includes the RAISERROR code below? RAISERROR('Attempt to modify supposedly immutable number.',16,1) How will we know this ...
1
vote
1answer
25 views

Configure MariaDB to log row deletion ONLY

I'm investigating some rows/tables that intermittently go missing on a Magento site, and of course there's no entries in the error log. (I suspect Magento queries/rules or human error might be the ...
2
votes
2answers
589 views

How to reference only affected rows in AFTER UPDATE trigger

I have this table: And I'm trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number ...
0
votes
0answers
34 views

Increasing system load due to multiple inserts every millisecond

In my project I need to do multiple database inserts every millisecond and thus Postgres takes huge amount of CPU. I need to find a way to reduce CPU load as I need to process about millions of rows. ...
0
votes
0answers
91 views

Trigger on Update of Specific column with aggregate function

I have two tables. Table DNF and table Programs. I would like to update the Programs table offbudget column only when there is some modification made in the ActualBudget column of the DNF table that ...
0
votes
0answers
23 views

Which trigger inserted data the table

I have 3 triggers which insert data to a table. I would like to find out which one is inserting the data. How do I search. The triggers are on another schema I do not have access to. oracle 11g
3
votes
2answers
204 views

How to capture sp_refreshsqlmodule event

My issue: executing sp_refreshsqlmodule will update the sys.objects.modify_date value, but does not fire a DDL event that can be used by a DDL trigger. So if using a DDL trigger to show object change ...
0
votes
2answers
82 views

Elements inside a container: How to perform atomic operations on them?

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null). Each element ...
0
votes
0answers
58 views

Trigger junction table to closure table on insert without infinite loop

I have a junction table for many to many relationships. I need to construct a trigger on insert to insert into a closure table that will NOT create an infinite loop. the DBMS is MySQL. My concept ...
0
votes
0answers
81 views

Trigger (insert/update) and procedure Required to encrypt data in MySQL

Is there any good way of doing encryption/decryption by use of triggers and procedure in MySQL. Looking for following to achieve in sequence Unload data for specific column to some other ...
0
votes
1answer
35 views

How do I stamp nothing?

I get an LDAP feed in for my employee database - basically a big csv file. This updates once a night and I revise all of our internal apps based on this spreadsheet. When a new person comes on the ...
0
votes
1answer
77 views

Two Triggers affect the same row

I've have triggers on two separate tables that are designed to affect a third table, the problem is each trigger affects different rows. The third table, C, currently looking like this: 00000010 | ...