Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

5
votes
0answers
115 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 ...
3
votes
0answers
30 views

Should I do the maths within PostgreSQL for ACIDity?

It is my first question on Database Administration Stack Exchange. Please be kind if something is not done the nice way.. I would like to be advised concerning the way I'm working on my database, in ...
3
votes
0answers
158 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 ...
3
votes
0answers
530 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
85 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 ...
3
votes
0answers
71 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
437 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
26 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 ...
2
votes
0answers
48 views

Mysql History Trigger Auto Increment bug or problem

I want to log history of a table, I created a history table and triggers for update,insert and delete operations. I do my test on realtime replication slave. When a record inserted master's main ...
2
votes
0answers
47 views

Pass additional data in connection for use by PGSQL Trigger

I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started. What I would ...
2
votes
0answers
255 views

Cannot update parent rows with trigger on the same table

I currently have the following two tables in my database CREATE TABLE folders ( id NUMBER NOT NULL, name VARCHAR2(100) NOT NULL, bytes NUMBER DEFAULT 0 NOT NULL, parentId NUMBER, CONSTRAINT ...
2
votes
0answers
73 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
201 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
189 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
481 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
631 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
144 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
66 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
109 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
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 ...
1
vote
0answers
104 views

Migrating schema from Entity-Attribute-Value (EAV) model to proper tables

I have an Entity-Attribute-Value (EAV) database in Oracle 11. For many, many reasons, I would like to model this using actual tables. Changing all the client code at once is not possible. Existing ...
1
vote
0answers
45 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
36 views

Mysteriously Updated Rows

Here's 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 GO ...
1
vote
0answers
311 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
141 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
55 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
321 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
142 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
160 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
38 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
109 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
78 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
639 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
84 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
602 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
271 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
121 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
11 views

Create update Trigger

I'm trying to create a trigger that whenever a software package is installed on a PC, the person who installed the software needs to provide data for pack, tagnum, and instdate, and the softcost field ...
0
votes
0answers
4 views

variable declaration and alteration in a trigger

For instance, I have a table with 3 different columns. Those columns are all defined as varchars. I insert 2 of those columns and i want a trigger, which generates the third column by merging the 2 ...
0
votes
0answers
54 views

After update triggers that deal with multiple column updates

I want to create a table for audit (audit in the sense of recording whatever a user has done in terms of data manipulation – e.g. update first name, delete contact etc.). For that I need to write a ...
0
votes
0answers
12 views

Prevent Update/Insert/Delete based on Date

I have a simple Table that holds financial data with a date. I would like to endure, that historic values are protected to INSERT, UPDATE or DELETE. Example: All values of March 2016 should not be ...
0
votes
0answers
35 views

Serial Function - PostgreSQL 9.2

I have the following column: users.code CHARACTER VARYING The customer can add any value he wants into that column, but he also can chose by not adding any value. That column can't be null, so a ...
0
votes
0answers
32 views

SQL Server Trigger: Move deleted rows from 4 different tables to an archive table

I have a garage database with 20 tables. 4 of these tables - tCar, tCustomer, tAddress and tTelephone hold data about when we service a car. After a few months I want to be able to delete the data ...
0
votes
0answers
8 views

Execution order of triggers for different tables in SQL Server 2008

There is a lot of material that covers the case where there are multiple triggers for one table but what about the case where there are two related tables, each with a trigger, e.g. an update triggers ...
0
votes
0answers
35 views

Query hung or way too long?

As part of the proccess of new items added to our system, some tables and relations are created programmaticaly via trigger, but it seems to be failing quite often right now. The DDL seems to be ...
0
votes
0answers
29 views

How to replicate SQL Server rowversion behaviour for some fields only

SQL Server version 10.50.4000. I have a table: create table people ( national_id char(16), full_name char(255), last_visited datetime ) I want to add a field that will work exactly ...
0
votes
0answers
14 views

Is automatic disabling of DML triggers possible during SSDT publish?

During ssdt deployment I am concerned about having DML triggers in place on tables that may slow down performance of table recreation a lot. We are facing the issue that the column order in the ...
0
votes
0answers
30 views

Backing up of database

I have a live MySQL DB and a Back up DB. What I would like to do is, once any changes are made to the live DB, the back up DB need to be immediately updated. I had thought about Triggers but the DB ...