Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
1answer
60 views

What is the best approach to process huge amount of data insertion efficiently

I'm dealing with a HUGE amount of data entry. I need to know the best practice to insert/update table records after new entry inserted in another table. By huge i mean we get more than 2 million ...
0
votes
0answers
28 views

Replication overview

My boss wants me to elaborate a comprehensive report about synchronisation/replication. I know that replication can be done with the DBMS (we are using SQL Server 2012), but he wants something manual ...
1
vote
1answer
12 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 ...
0
votes
1answer
38 views

How to create a Postgres trigger to ensure field is non-updateable

I'm trying to create a Postgres trigger to ensure after a column's value has been set, it cannot be updated (basically make it readonly). So far I've come up with the following draft trigger. 1) ...
0
votes
0answers
11 views

DB2 Trigger for EVENT and ARCHIVE TABLE

I have a requirement on DB2. There is a two Table EVENT and ARCHIVE. Both table has ROW_ID and STATUS field. Case 1: When Update STATUS Zero to One at EVENT table that row will insert at ARCHIVE ...
1
vote
1answer
20 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
34 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 ...
1
vote
2answers
21 views

MySQL After Update Trigger apparently not running

I have the following After Update trigger on a MySQL table: CREATE DEFINER = 'root'@'localhost' TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD AFTER UPDATE ON ...
0
votes
1answer
14 views

Getting the value from a column during a trigger

After a test completes (on a SQL 2008R2 system), the system updates a row in the TestRun table to include an end time. I am creating a trigger that will take action after that update, and do some ...
0
votes
0answers
16 views

SQL Server 2000 - Revert trigger template to original default

In SQL Server Enterprise Manager, in the table list for a database, you can right click on a table, choose 'All Tasks' and then 'Manage Triggers ...' which will open up the following window I had to ...
3
votes
1answer
33 views

PostgreSQL: Trigger for updating view doesn't work

I'm trying to make the trigger for updating a view work. If I'm doing and INSERT it works! But when I'm trying to do an UPDATE it just doesn't run. CREATE OR REPLACE FUNCTION test_trigger_function() ...
0
votes
0answers
23 views

How do triggers work on partitioned tables?

I have partitioned a table on the basis of a column. This column can either have 0 or 1. So, only two partitions (p0, p1) have been created internally. On this table there are 3 triggers which execute ...
0
votes
0answers
38 views

Copying a table with its structure, indexes, and associated triggers?

I have studied this post on Stack Overflow: Fastest way to copy a table in MySQL. But no answer is copying everything like the table's structure, data, indexes as well as associated triggers. Is it ...
1
vote
1answer
24 views

How to get the rowid in a TRIGGER for another table in SQLite

Given the below schema CREATE TABLE Item ( _id INTEGER NOT NULL, name NVARCHAR NOT NULL, parent INTEGER NULL CONSTRAINT fk_Recursive ...
0
votes
0answers
12 views

Access Sql Server Linked Server during Logon Trigger

I am trying to log users logons to my Sql Server (A) in another Sql Server (B, different location physically), but when I try to log in I get the error Login failed ... due to trigger execution and ...
0
votes
1answer
41 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 ...
1
vote
1answer
31 views

Why/when does creating a mysql trigger require SUPER privileges?

I'm attempting to parse and understand what the MySQL doc says about when SUPER privileges are required for creating triggers. They say: If binary logging is enabled, CREATE TRIGGER might require ...
1
vote
1answer
39 views

PostgreSQL 9.3: Primary key violated by a trigger INSERT

My problem Consider a table t with many frequent updates from users, from which only the last few are relevant. In order to keep the table size reasonable, whenever a new row is inserted old rows ...
3
votes
2answers
49 views

How to tell what invoked a trigger in SQL Server?

I'm working on a legacy SQL Server application, with close to 3,000 tables and more than 10,000 stored procedures. One of those stored procedures is deleting some needed data from a table. I don't ...
0
votes
1answer
42 views

sql expiry date use Trigger OR Schedule a Job

I have this table : Now, i like the way when the date expires. EndTime < GETDATE() expire field is true. What method should I use? use a trigger to true or a schedule job that run before run ...
0
votes
1answer
27 views

Trigger on a Schema

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one. --Database level trigger. ...
0
votes
1answer
59 views

Oracle Triggers - Before Update and Before Insert - Separate or Combined?

I'm running Oracle DB 9i. I have a table with various constraints to ensure data integrity. In addition to the constraints, I have triggers on BEFORE INSERT and BEFORE UPDATE to ensure that ...
0
votes
1answer
30 views

MySQL Syntax Error When Creating a Trigger

I am trying to create a trigger but I am getting a syntax error and I am not really sure why I am. CREATE TRIGGER Section_Insert AFTER INSERT ON Section -> FOR EACH ROW BEGIN -> INSERT ...
2
votes
1answer
24 views

Can triggers be used to implement automated polymorphic association?

(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.) I'm attempting to (re)design a MySQL database for a personal social ...
3
votes
1answer
48 views

Use of Trigger After a Transaction

I have a scenario where there is a transaction being executed to update a few tables. On one of these tables there is a trigger that accesses the data on those tables, but is only planned to be on ...
0
votes
2answers
47 views

Dynamic permissions

I have a application that creates tables in one user's schema and another user needs to be able to use the dynamically created tables. Example: Application creates User1.RandomTable User2 needs ...
1
vote
1answer
28 views

How to create trigger with NOT FOR REPLICATION

I tried adding "NOT FOR REPLICATION " in many parts in the below code but ended up in the below code. I searched a lot for an example where I can see how its done with a trigger. Can any one show me ...
0
votes
0answers
29 views

Oracle Archiving deleted rows via trigger and trigger logging if error

I created table in new schema/user CREATE TABLE archive.table1 ( id NUMBER(10), name VARCHAR(45), created_at DATE, updated_at DATE, deleted_at DATE ); I granted access to that table for ...
0
votes
1answer
51 views

ALTER TABLE AUTO_INCREMENT number in a Trigger

I want to have my auto increment column to increment by step of 3 upon insert. Can I do something like: Alter table `mytable` auto_increment = (select max(id) from mytable) + 3; in a Trigger? I ...
1
vote
2answers
51 views

[SOLVED]The right syntax for handling an UPDATE trigger and logging what happened per column?

SOLVED The users table: CREATE TABLE `users` ( `id` int(8) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(45) DEFAULT NULL, `username` varchar(16) DEFAULT NULL, `salt` varchar(16) DEFAULT ...
1
vote
1answer
47 views

Mutating Error , How to solve it

CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER (10); ...
2
votes
0answers
84 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 ...
0
votes
1answer
46 views

Inhibit SQL Server trigger execution

A table with various prices per item have a trigger that will update price 3 when price 0 is changed. The ERP that uses this database has a utility that when run updates the database with ...
2
votes
1answer
34 views

Rewrite sqlite trigger to postgresql

Can you help me please with this problem? I need rewrite this simply trigger from sqlite to PostgreSQL format: In sqlite: CREATE TRIGGER connections_INSERT_update_connection_root_trg ...
1
vote
1answer
88 views

Why do I get error 1054 using trigger on INSERT saying that column isn't on the field list?

I am trying to write a Trigger to register every new inserted genre into a news table. Here are the tables definitions. They are only two, GENEROS & NOVEDADES. This is the trigger: CREATE ...
1
vote
2answers
69 views

how should i do this trigger?

I am a newbie to the sql world :) Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ? Here is the code : DELIMITER ...
2
votes
2answers
92 views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
0
votes
1answer
151 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
73 views

How to support multiple updates in this trigger?

Below is the code of a trigger, basically it calls a function in case the record that is being updated does not have other row associated by it's Id in another table. In a multiple row update this ...
1
vote
1answer
151 views

Does the Inserted table contain Identity values?

I have an issue with this AFTER INSERT trigger that I'm using for auditing: Cannot insert the value NULL into column 'TermID', table 'AuditTerms'; column does not allow nulls. INSERT fails. ...
0
votes
0answers
29 views

Oracle after insert trigger vs update performance in plsql block

I want to know whether an after insert trigger is better than an insertion statement followed by update statement in plsql block.
1
vote
0answers
27 views

Preventing unique key constraint violation when pre-existing row has 'expired' [duplicate]

I have the following table schema; CREATE TABLE LOCKS ( lock_uid INTEGER NOT NULL, file_uid VARCHAR2(32 char) NOT NULL UNIQUE, owner VARCHAR2(255 char) NOT NULL, expires DATE NOT ...
1
vote
0answers
69 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 ...
0
votes
1answer
25 views

Trying to get this MySQL Trigger working

create trigger check_td before for each row begin declare v_code_ID varchar(25); declare c_Worker1 cursor for select code_ID from Worker1; declare c_Worker2 cursor for select code_ID from Worker2; ...
1
vote
1answer
129 views

Intentionally recursive instead of insert trigger does not want to nest

I have been struggling to get an instead of trigger to recursively call itself. The idea is that when a row gets inserted into my table the trigger is placed on, based upon certain conditions ...
1
vote
1answer
359 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; ...
0
votes
0answers
40 views

Load XML is not working in mysql version 5.5

I have a requirement where i need to load xml file into a mysql table using trigger. I have written the following trigger to achieve this. CREATE TRIGGER asdb.payment_test AFTER INSERT ON asdb.abc1 ...
1
vote
3answers
82 views

Mysql to Oracle communication

I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server). The orders ...
3
votes
1answer
125 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
0
votes
1answer
950 views

Trigger to update a date field on one table when another table is updated

I want to be able to update a date filed in one table when another table is updated. I have the following two tables: Transdetails, Customers. In the Customers table I have a field called ...