Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
0answers
7 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
0answers
16 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 ...
1
vote
1answer
16 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
24 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
42 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
19 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
19 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
34 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
44 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
43 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
54 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
43 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
27 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
66 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
68 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
74 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
67 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
65 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
123 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
18 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 ...
0
votes
0answers
50 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
22 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
95 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
153 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
34 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
66 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
105 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
373 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 ...
1
vote
0answers
84 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 ?
2
votes
1answer
84 views

Do triggers affect DB performance

I have a database with 250 small and medium sized tables. These tables are related with both MyISAM and InnoDB storage engines. There are multiple triggers that have been created on different tables. ...
0
votes
3answers
71 views

Trigger or Stored Procedure?

Let's say I have two tables, foo and bar: CREATE TABLE foo( foo_id serial NOT NULL, count integer NULL, PRIMARY KEY(foo_id) ); CREATE TABLE bar( bar_id serial NOT ...
-1
votes
1answer
78 views

Access table field on creating a trigger postgresql

How can I access table field on creating a trigger? CREATE TRIGGER foo_trigger AFTER INSERT OR UPDATE OR DELETE ON foo FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
0
votes
0answers
70 views

Problem with a trigger (Oracle 11gR2)

I converted the trigger into a "Compound" trigger; this is my first attempt using a compound trigger. Here is my new code: --select * from user_errors; CREATE OR REPLACE TRIGGER ai_fin_transaction FOR ...
0
votes
2answers
61 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
1answer
146 views

I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view

I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation ...
7
votes
1answer
278 views

Synchronization using triggers

I have a requirement similar to previous discussions at: Writing a simple bank schema: How should I keep my balances in sync with their transaction history? Trigger in combination with transaction ...
0
votes
0answers
29 views

What is the story about Oracle CREATE TRIGGER … CALL syntax?

I was able to use the CREATE TRIGGER ... CALL SOMEPROCEDURE; syntax but I'm a little bewildered about it. I expected to see some implicit arguments like TABLE%ROWTYPE new but that doesn't seem to be ...
4
votes
1answer
78 views

Is it possible to return a custom error for a logon trigger?

I'm trying to get a logon trigger to return a custom error. Thus far everything I have tried causes it to return the standard error: My trigger looks like this: CREATE TRIGGER Test_Logon_Trigger ...
0
votes
0answers
44 views

How to update Status using IF Condition in Mysql trigger

I'm trying to write an update trigger that will Design_Update_Status to Customized when a date is entered in Custom_Design_Update_Date field and if nothing is entered that field it should update ...
2
votes
1answer
120 views

How should I limit a table to N rows, discarding oldest?

I have a SQL Server table which I use to store events. I only want the table to contain the 1000 most recently added rows, when row 1001 is inserted, row 1 should be deleted. I have both a ...
2
votes
1answer
114 views

How to reuse an update trigger for multiple tables in postgresql?

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table: CREATE TRIGGER ...
0
votes
0answers
38 views

How to analyze incoming DML in SQL Server 2005?

I have database and specifically one table that is used for batch data transfer from another organization. With each run the table is truncated and rows are re-inserted. I want to handle that process ...
0
votes
1answer
52 views

How to ensure a trigger is fired after variable number of inserts?

I have a 1:N relationship between two tables - let's call them "foo" (the parent table) and "bar". I have some triggers on both "foo" and "bar" tables. I insert data in a single transaction, first to ...
3
votes
1answer
63 views

Can't update trigger because of activity

Trying to add a line to a trigger that is attached to a table that is constantly in use ALTER TRIGGER [dbo].[name_of_trigger] ON [dbo].[name_of_table] AFTER UPDATE BEGIN IF ORIGINAL_LOGIN() in ...
4
votes
1answer
117 views

How to access NEW or OLD field given only the field's name?

I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
0
votes
0answers
20 views

Any possible solution for triggers associated with a table which is renamed to new name as trigger code still points to old table name

If there are any triggers associated with a table which is renamed to new name as trigger code still points to old table name As mysql document it says statement fails with the error Trigger in ...
1
vote
2answers
92 views

Basic trigger in PL/pgSQL, to flip a boolean field if another field has text

I want a BOOLEAN field to be set to TRUE when a sibling TEXT field is set to a non-empty string. Vice versa, the BOOLEAN is set to FALSE when TEXT field is set to empty string. The following function ...
0
votes
2answers
91 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 ...
-2
votes
1answer
307 views

how to autoincrement varchar datatype depending upon the product details name using trigger

I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id ...