Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

1
vote
0answers
11 views

creating deadlock for distributed database in mysql-(Testing purpose)

I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
1
vote
0answers
9 views

MySQL performance of trigger when many rows are affected

I have a question on MySQL triggers which I hope someone might be able to help with. As part of our platform, we have a table (accounting), which grows rapidly. We also have an archived version of ...
0
votes
1answer
30 views

Oracle SQL Complications With Multiple Triggers

Hey all so I am creating a DB that is used going to be used for managing utilities of a system. My DB has 3 tables: Users, Utilites, and Files (leaving Files table out because question shouldn't ...
0
votes
1answer
2k views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
1
vote
1answer
142 views

Generic Trigger on MYSQL to replicate data on other table

I'm currently doing a history table that basically logs every single modification in a history table, and adding the Action executed. CREATE TABLE `competencies` ( `id` int(11) NOT NULL ...
1
vote
1answer
30 views

trigger or check constraint insert values based upon existence of others

For simplicity (and since it captures the essence of my problem), suppose I have a table with data. CREATE TABLE my_practice_table ( my_id NUMBER(12), field_2 VARCHAR2(20), field_3 ...
5
votes
1answer
7k views

Why I don't need to COMMIT in database trigger?

We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database ...
0
votes
1answer
168 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
0answers
35 views

Indexing on cached data from related table using triggers — Is there a better way?

Edit: Schema details per request from Aaron Betrand -- I'll break the schema down to the relevant fields of the two tables. Orders (ID int IDENTITY, Status char(1)) Services(OrderID FOREIGN KEY ...
0
votes
0answers
15 views

Mysql 5.0 and 5.6 INSERT_LAST_ID() difference

Migrating from MySQL 5.0 to 5.6 i noticed that the INSERT_LAST_ID() function behaves different when using inside a trigger. My simplified situation: Table A has the trigger: insert into table_B ...
2
votes
2answers
140 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 ...
1
vote
1answer
192 views

Mysql - Select To XML

I'm Building Audit Trail with Trigger, and want to convert the inserted / updated / deleted value into XML and store it into another table. my trigger look like this: DELIMITER $$ CREATE ...
0
votes
2answers
189 views

Error in trigger: table X is mutating, trigger/function may not see it

Having looked at the following Trigger, still getting the Row 2 error: "ORA-04091: table SUMMITTEST.APL_LOG is mutating, trigger/function may not see it" Please can anyone provide any help in ...
0
votes
1answer
16 views

On triggers and replication in MySQL

I have a classical statement-based replication between the master and the slave. Triggers were created on the master and replicated on the slave as well. Assuming that I have, say an ON UPDATE ...
-2
votes
2answers
38 views

UPDATE() Function (Insert and Update Triggers)

Is there a way to have the column name dynamic in this function? For example, passing a parameter to a variable named @FldName = 'Name'. e.g. UPDATE(@FldName). Basically, what I want to do is to have ...
0
votes
2answers
19 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 ...
0
votes
3answers
244 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
0
votes
4answers
16k views

Create a trigger to update table data on another Server's database

I am creating a trigger in MySQL and I need a little help. I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2. These databases have the same tables names. I want ...
0
votes
1answer
35 views

MySQL: Capture every 5 values from column a in Table A to create one row in Table B

I have submissions of a form from a web page records in a database. the table has the following schema submit_time form_name field_name field_value field_order file the form has 5 fields, ...
0
votes
2answers
340 views

Mysql trigger not working, syntax issues

I have this trigger CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; END; mysql 5.1.72-0ubuntu0.10.04.1 ...
1
vote
0answers
52 views

SQL Server inserted/deleted tables [closed]

I have the following logic flow Start transaction insert record 1 into table1 insert record 2 into table1 insert record 3 into table1 commit transaction I have a trigger on table1 that is using ...
0
votes
1answer
172 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
1answer
22 views

Can I setup database triggers to run on a defined secondary set of servers only? [closed]

This might sound a bit off, but here's what I have been thinking for a while now: Use Case You want to build an activity log for each user action on your application using database (postgreSQL) ...
2
votes
2answers
76 views

Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error

I recently upgraded from SQL Server 2005 to 2012. During validation however, a bug was discovered. A certain trigger was coded as follows: CREATE TRIGGER [dbo].[trigger] on [dbo].[foo] FOR UPDATE, ...
0
votes
1answer
54 views

Trigger function check if all NEW.* fields exist in another table

In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv". ...
0
votes
2answers
30 views

How to use a trigger to only insert one row

I am doing a third year project using an intel galileo that reads an RFID card, and signs a student into a class, and I am using multiple tables. I have nearly completed it as all reads are been ...
0
votes
0answers
36 views

PostgreSQL trigger copy record to another table with different columns order

I have a trigger that before every update on hundreds of tables it inserts into a table with the same name and the _hv suffix a copy of the record being updated to maintain a kind of historical ...
0
votes
1answer
35 views

SQL Server Transactional Replication With subscriber trigger returning a value

I have transaction replication setup on on SQL server 2008 R2. The subscriber has a after trigger on the published article that inserts into another table on the subscriber's database and returns the ...
0
votes
1answer
62 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 ...
1
vote
0answers
28 views

How do I ensure that bulk inserts must fire my DML trigger?

I have a table using a DML trigger to ensure data integrity. Although authors of SSIS packages, BULK INSERT queries, and bcp executions can enable DML trigger execution, which is disabled by default, ...
0
votes
1answer
178 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
2
votes
1answer
2k views

SQL Server trigger (update or insert with inner join problem)

ALTER TRIGGER [dbo].[killertrigg] ON [dbo].[tbl_pvporderview] AFTER UPDATE AS IF (UPDATE([Kill])) BEGIN SET NOCOUNT ON; INSERT killer(serial,[Kill]) SELECT i.serial,i.[Kill] FROM ...
0
votes
1answer
25 views

how to delete all rows with empty field via function/trigger combo in Postgres v 9.3?

In PostgreSQL 9.3 I am trying to write a trigger function that will be called after updates to table test2 and that will delete rows of test2 where field "email" is empty. Here's my attempt: CREATE ...
2
votes
2answers
3k 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
0answers
26 views

Do Statement-level AFTER triggers occur asynchronously after the statement returns?

I'm using an AFTER INSERT OR DELETE OR TRUNCATE ... FOR EACH STATEMENT trigger on a table to refresh a materialized view, and I'm concerned that it will significantly affect the performance of those ...
2
votes
2answers
1k views

Is it possible to monitor role and privilege grant/revoke using triggers?

Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.
1
vote
2answers
4k views

Trigger to audit table changes

I have created a trigger to audit changes to a table: CREATE TRIGGER [dbo].[iudt_AutoAuditChanges] ON dbo.CPTCategoryMaster AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; ...
1
vote
1answer
48 views

Concurrency when simulating autoincrement with triggers on MySQL

I am updating an old application to implement virtual tenancy, which involves adding a tenant_id column to the primary key of every single table. The application uses auto-incrementing columns ...
0
votes
0answers
25 views

Oracle SQL Updating Trigger Statement

Hey all so let me see if I can clearly explain what I would like this trigger to exactly do. First of all the trigger will deal with 2 tables: Delivery Table: DeliveryNum |ItemName | Quantity | ...
1
vote
1answer
23 views

Trigger to apply user mapping to logins on database creation [closed]

Does anyone have any example triggers that apply user mapping on database creation?
0
votes
1answer
16 views

MySQL Replication, Can i add a trigger to the SLAVE db?

Is it possible to do so? The trigger will insert some rows into a unique table that exists only on the slave.
2
votes
2answers
703 views

Error updating sql server geography type in trigger

I have the following trigger in my database: CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS IF UPDATE(Latitude) UPDATE pt SET pt.GeocodedAddress = ...
1
vote
0answers
815 views

DDL trigger AFTER ALTER ON SCHEMA behavior

Can anyone please tell me what's going on here: CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA cols ora_name_list_t; BEGIN IF (ora_dict_obj_type = 'TABLE') THEN select column_name bulk ...
0
votes
0answers
22 views

Efficient Trigger updating in Postgresql

So I have a big table that often needs specific columns updated for a large number of rows. So right now I have it set up so that there is a 'staging' table that has the update-able columns. Then upon ...
1
vote
3answers
835 views

How to create a SQL trigger to execute a stored procedure when a table is updated

i need some help with creating a trigger to run a qf.stored_procedure when a qf.table is created. AT the moment, i have a job t run the stored procedure every 15 minutes but changing that is not an ...
0
votes
0answers
36 views

Delete data from related Tables with condition

I have Table a (id, id_d); --fk contraints = on delete cascade Table b (id, language_code); Table c (id_a, id_b); --fk contraints = on delete cascade Table d (id); I want to delete an entry from ...
0
votes
1answer
24 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
0answers
32 views

Better way to create an Events table?

I working on a medium sized PostgreSQL database with some schemas, each with some tables. In almost every schema there are tables that represent event tables, where something that has "happened to" ...
1
vote
1answer
75 views

MYSQL-BEFORE UPDATE Set value of Column A to value in Column B if Column A is to be updated

I have a nightly scheduled event which updates all rows in 'products' table with the newest 'unit_price' from a price list table where 'price_list.active-date' is >= the CURDATE(). What I need to do ...
0
votes
1answer
82 views

Unable to update or insert values table with DateTime column defaulted to GETDATE

I have atlered table by adding Datetime column. This column is not null and also default value from GETDATE(). ALTER TABLE myTable ADD myDateColun DATETIME NOT NULL DEFAULT (GETDATE()) I'm trying ...