Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

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
115 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
44 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
58 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
141 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
20 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); ...
1
vote
1answer
313 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
375 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
76 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
31 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
244 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
412 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
151 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
191 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
112 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
929 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) = Replace(...
0
votes
1answer
77 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
203 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
24 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
129 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
16 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
49 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
58 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
28 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 ...
3
votes
2answers
2k 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
36 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
106 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
244 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
73 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
94 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
79 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 | ...
1
vote
1answer
265 views

After Insert Trigger never fires using Import Wizard

Using Import Wizard I tried importing tables tcc_Block and PROJECT_IDENTITY. These 2 table structures already exist in SQL Server and they are related with ProjectID columns. PROJECT_IDENTITY has ...
2
votes
1answer
516 views

Instead of insert trigger VS For insert Triggers in SQL server

Edited: My question is since there is an insert statement in the instead off insert trigger, then it should call insert trigger from inside the instead off insert trigger and which should avoid ...
3
votes
1answer
177 views

Prevent explicit insert into serial columns

I want to prevent explicit inserts into serial columns. I have come up to the following trigger: drop table test_table; create table test_table( id bigserial primary key, foobar text ); create or ...
0
votes
1answer
137 views

How to profile trigger database performance impact

I want to set up some triggers on a DB2 box (LUW, 9.7). The DBAs are concerned that if the trigger fires too often, it will adversely affect general performance. What's a reasonable cheap approach ...
1
vote
1answer
227 views

How to remove triggers from the restored database in SQL Server

I am using SQL Server 2008 Management Studio. I've got a .bak backup file from a client to resolve a specific issue. Once I restore the database from the .bak file, I am unable to do any alter query ...
1
vote
1answer
6k views

Create Trigger MySql update or insert in another table

I am trying to learn more about triggers in mysql, so bear with me. I have two tables, TestTable (id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Date date, Value REAL) TestTable2 (id ...
3
votes
0answers
198 views

Preventing Infinite Trigger Loops in PostgreSQL

I am working with two PostgreSQL databases (one internal and one on our company Web server) where I am attempting to share data between the two instances through the use of triggers. The databases ...
0
votes
0answers
142 views

How to implement a query insert into BEFORE INSERT trigger syntax

I want to insert data using trigger into 2 tables. I want to implement my query insert to a before insert trigger. Since I was newbie for using MYSQL, I'm confused how to implement it. insert into ...
2
votes
0answers
44 views

postgresql: deleting from a view

In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on ...
0
votes
1answer
117 views

MySQL Trigger Update

How do I update 'emailupdate' date with a trigger when 'email' is updated through a script? +------+----------------+--------------+ | id | email | emailupdate | +------+----------------+-...
2
votes
1answer
347 views

MySQL BEFORE INSERT trigger with IF conditions

In my BEFORE INSERT trigger, I select data, then I check the value if it's null then I do insert data into table A then else into table B. I select data from a table in another database called ebsms....
1
vote
1answer
119 views

Oracle: Mutually modifying two tables?

Suppose I have two tables I want to keep in sync: create table a(x number, s varchar2(10)); create table b(x number, s varchar2(10)); It's easy to keep B synchronized with A, using triggers such as:...
1
vote
2answers
276 views

MySQL - How to make a DATEDIFF in years?

This is my first post (and English is not my mother tongue) so I hope I won't screw this post up. I am new to MySQL but I learned a lot with 100+ youtube tutorials and your numerous posts. I designed ...