Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

3
votes
0answers
50 views

Which is the right way to fix “Error: 1205 Lock wait timeout exceeded; try restarting transaction”

I have a PHP script that sync a local DB against external datasource. Each time the script runs I perform some triggers on the DB where INSERT or UPDATE happen. These are the the triggers for that ...
3
votes
0answers
43 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. ...
3
votes
0answers
227 views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
2
votes
0answers
24 views

Options for copying data out of continuously updating tables

I work with a pretty large (O(10^1) TB) DB2 (LUW, v. 9.7) database. It's got data coming in on a continuous basis via Golden Gate replication from another DB2 database. It's used for business ...
2
votes
0answers
35 views

Server Logon Trigger fails after deleting many records in “Logon-Table” -SQL Error 2801

We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table. The table doesn't have clustered index, but it does have a ...
2
votes
0answers
42 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
2
votes
0answers
56 views

Mysql Trigger use inserted value as column name for update After Insert

I have 2 tables: One for storing order_products and another one for keeping inventory levels for multiple stores The order_products table keeps the ID for the store in the store_id column. ...
2
votes
0answers
118 views

Oracle instead of trigger update performance issue

I have a large table containing over 15million records with 4 columns that form a compound key. Because we use a development environment that doesn't support compound keys I was required to create a ...
2
votes
0answers
326 views

AFTER DELETE trigger will not fire during a cascade delete

I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong. In addition, each Aisle has a Node at which it starts and ends. Aisle Table: Aisle char(4) Start_Node ...
2
votes
0answers
433 views

Postgresql trigger - get all changed rows after transaction

I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction. CREATE ...
2
votes
0answers
127 views

logon trigger to send an email

I want to create a simple login trigger. basically if user = test , then send an email to me. CREATE TRIGGER ServerLogon ON ALL SERVER WITH EXECUTE AS 'test' FOR LOGON AS BEGIN IF ...
2
votes
0answers
56 views

Setting up triggers based on exposed columns in a view's source table

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view CREATE VIEW [dbo].[iv_EmployeesView] AS SELECT EMPLOYEE_GUID AS EmployeeGuid, ...
2
votes
0answers
105 views

SQL Server and updating windows firewall rules

Any ideas how should I proceed if I would like to update the firewall rules (essentially add ip-s to existing firewall rule). The step of exporting ip-s to text file and then using command line to ...
1
vote
0answers
19 views

trigger to first validate values in other table and then update

I have problem coding a trigger. The table structure is this: table 1: inventory field 1: item_code(PK) field 2: item_desc field 3: unit_price table 2: stock_master field 1: item_code(fk) ...
1
vote
0answers
25 views

Mysteriously Updated Rows

Yo, heres the trigger causing the Problem USE [SalesDb] GO /****** Object: Trigger [dbo].[trgAfterUpdate] Script Date: 7/16/2015 6:34:01 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON ...
1
vote
0answers
18 views

How Columns_update() Function Works in Trigger To Find Updated Columns Name

I am in confuse about trigger function Columns_Update(). i Have Basic Idea about this function that it returns a varbinary and it is use to get updated Columns in Trigger even i have Find The code ...
1
vote
0answers
43 views

Trigger failing when called using sp_executesql

We have a recursive trigger on a table that executes AFTER INSERT. There is a check to ensure the trigger does not recurse more than 32 levels deep: IF(SELECT TRIGGER_NESTLEVEL()) >= 31 ...
1
vote
0answers
24 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 ...
1
vote
0answers
52 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, ...
1
vote
0answers
66 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
0answers
60 views

How to set the trigger when field(contain json value) is updated?

I have one table in which there is one field called info.This field hold json value. I want to create trigger on this field.I want to split this json format string into record I mean I want to insert ...
1
vote
0answers
32 views

Error when creating Trigger (PostgreSQL 9.4)

I created a table 'Company', and I want to create a trigger so a company can only present in one city, but it can be present in several different cities in the same country. For example, there's only ...
1
vote
0answers
68 views

Make the SQL Database manage CRUD operations

Is it possible to have a client app send a transaction of INSERTs to a table. Then have the table triggered somehow to run through the inserts and either INSERT the row or UPDATE an existing row? If ...
1
vote
0answers
52 views

How To Log Select Statements from 1 User On 1 Table Into a Permanent Log Table

We have a centralized authentication library that 100+ applications use to authenticate users. That library has its own DB credentials. It would be extremely useful to log every time that library/user ...
1
vote
0answers
353 views

Debugging Postgres 9.3 Triggers

Right now I have a trigger in my database that is giving me some issues. I've set up this line so I can check what is being fetched from a query RAISE NOTICE 'hstore_key = %',hstore_key; as well I ...
1
vote
0answers
81 views

Implementing a functionality for processing heavy insertion

I use PostgreSQL 9.1. The scenario is: I receive heavy insertion into Table1 (about 100 rows per sec). For each new row, I have to check the previous and next row of the source table if those are ...
1
vote
0answers
290 views

How to avoid locking problems while disabling/enabling triggers in a PostgreSQL function?

Whenever I try to disable a trigger from within a function, I get following error: ERROR: cannot ALTER TABLE "datace" because it is being used by active queries in this session It seems logical ...
1
vote
0answers
206 views

Storing changes to stored procedure and triggers in PostgreSQL

I want a mechanism where I can store/track changes in stored procedure and triggers. I want to track these logs in some table. What is the best possible way ?
1
vote
0answers
1k 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 ...
1
vote
0answers
1k views

Why Oracle Database trigger can became DISABLED

We have an application, using ddl database trigger on oracle database. Trigger depends on package and several tables, package depends on user defined function. It works on different customer sites ...
1
vote
0answers
116 views

TSQL Trigger Incorrect Syntax

Im a SQL noob but here goes - I have a trigger in SQL which is giving me an incorrect syntax error E.G: INSERT INTO aTable ( tTypeId, opId, inserted.Id ) SELECT 3, 0, inserted.Id ...
0
votes
0answers
22 views

Creating a trigger to update field in 1 table based on values in another table using data in multiple rows

I'm new at SQL and need to create 2 triggers between 2 tables. Table 1 has the following columns: tbl1.ContractID char(6) tbl1.ContractFulfilled char(1) Table 2 has the following columns: ...
0
votes
0answers
21 views

What is the syntax to sp_recompile logon trigger?

I would like to recompile my logon trigger using sp_recompile. But I can't figure out the syntax. E.g. these don't work: sp_recompile '[Tr_Logon]' sp_recompile '[Tr_Logon]' ON ALL SERVER ;)
0
votes
0answers
35 views

CALL Procedure(Function) = Trigger?

I have discovered some strange behavior in Oracle 11g, and I'm wondering if anyone has an explanation for it. The gist is that when I use a function that contains a commit/rollback/savepoint within a ...
0
votes
0answers
53 views

Oracle trigger after insert error ORA-01403: no data found

I'm trying to make a trigger that select a value inserted in other table and concat with other value in an insert on other table that's my code CREATE TABLE "SEX" ( "ID" NUMBER, "CS" ...
0
votes
0answers
39 views

Loop through fields on a before-insert trigger?

Here's my table: CREATE TABLE foo ( i_am_pk CHARACTER VARYING(255) PRIMARY KEY NOT NULL, dont_touch_this CHARACTER VARYING(255) NOT NULL, name CHARACTER VARYING(255) NOT NULL, addr CHARACTER ...
0
votes
0answers
36 views

Mysql trigger on insert on another table

I have three tables all three have [product_id] column, what all i want to do is when there is new entry of quantity of respective [product_id] in table A and table B i want show the sum of the ...
0
votes
0answers
32 views

Which technologies should I consider using for creating a calculated view?

I use SQLServer, but I suppose this question is general enough for any type of database. Suppose I wish to build an application that allows people to rate geographic regions on where they would like ...
0
votes
0answers
48 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 ...
0
votes
0answers
104 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
0answers
43 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 ...
0
votes
0answers
40 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 | ...
0
votes
0answers
23 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 ...
0
votes
0answers
55 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
0answers
16 views

getting an error in trigger while invoking a procedure in it

i have created a procedure name 'prd' which contains 3 parameters (pmat_amt,pstartdate,pstopdate) which is used to update a column in the table rd_details contains 3 columns which are ...
0
votes
0answers
54 views

MySQL : How to know if Trigger was fired

Someone can help me please , I want to log error about triggers in MySQL 5.5 I'll like to know if it's possible to know if a trigger was'nt fired ?
0
votes
0answers
64 views

Did this trigger break replication?

I added a trigger to a replicated database. DELIMITER $$ USE `database`$$ DROP TRIGGER /*!50032 IF EXISTS */ `some_ins_after`$$ CREATE /*!50017 DEFINER = 'user'@'%' */ TRIGGER ...
0
votes
0answers
51 views

mysql trigger, let trigger recalculate throug whole table after correction of errors

Raised this question before but no answer found I have one table (table1) with energy data updated every 5 min I am using a trigger to calculate day values on every update and fill another table ...
0
votes
0answers
69 views

how to update 'update trigger fired record's column?

I'm new on this task. I tried to update when I/F table insert after trigger. but it wasn't work.. There is my trigger source like.. create trigger T_ATABLE_I after insert on ATABLE for each row ...