Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
1answer
166 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
32 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
14 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
136 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
188 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
169 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
15 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
35 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
18 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
240 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
15k 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
31 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
334 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
49 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
143 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
21 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
70 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
48 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
26 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
33 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
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
137 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 ...
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
53 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
27 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
176 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
22 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
25 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
46 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
21 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
15 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
696 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
790 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
666 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
35 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
23 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
27 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
71 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
77 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 ...
1
vote
2answers
44 views

Need to convert date to word using trigger

I have created a datetoword function in Mysql. Checked the function working fine. Now using this function need to convert the date from field1 (date-datetype) to field2 (Varchar-datatype) within the ...
1
vote
1answer
122 views

Can Execute trigger in SQL Server 2008 R2

I'm trying to create a simple trigger to update a table's column after it was inserted. But when I execute the trigger it produces an error: Cannot execute script. Additional information: Unable ...
0
votes
1answer
46 views

how to use trigger to make SQL Job's step success or fail

Step 1. Call a script to trigger application process. When the application process start, it will insert a record to a specific table. And once the process ends, that record will be deleted from ...
0
votes
1answer
138 views

trigger - Sql2008 - Msg 7391 - “no transaction is active/unable to begin trans”

I have a trigger on my SqlServer, located in server7. It inserts a data on another server ( server5 ), after insert, as you see the code below: CREATETRIGGER [dbo].[trgSPTInsereDepartamento] ON ...
0
votes
1answer
3k views

PLS-00049 bad bind variable when compiling a trigger

I'm trying to implement a dedupe trigger to a table. CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP BEFORE INSERT ON USER.ERRORTABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF ...