Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

1
vote
1answer
393 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
87 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 ...
3
votes
2answers
2k 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
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
125 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
613 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
84 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
29 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
205 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
34 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; ...
2
votes
1answer
452 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
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; ...
0
votes
0answers
102 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
165 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 ...
4
votes
1answer
390 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
6k 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
186 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
157 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. ...
2
votes
4answers
107 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
205 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
178 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 ...
0
votes
2answers
341 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
1k 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
1k 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
32 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 ...
3
votes
1answer
229 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
71 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
291 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 ...
3
votes
1answer
293 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
61 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
100 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
170 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 ...
5
votes
1answer
557 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
25 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
268 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
3answers
245 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 ...
-1
votes
1answer
2k 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 ...
2
votes
3answers
196 views

How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

I have two tables 1. Customer_details and 2. Order_details. 1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name, 2.Order_details table --> fields-- Ord_Id(Primary ...
1
vote
2answers
177 views

Triggers After Insert and After Update - Good Practice

It is a good practice to have a separate trigger for After Insert and another one for After Update or there is no problem in having both in the same logic? Also from a perspective of performance it ...
0
votes
0answers
135 views

Issue with a where clause by using Oracle

I'm currently facing a problem while I'm adding a trigger to the database that I use at home. I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production The problem ...
0
votes
2answers
38 views

Anyway to get the trigger to work?

I want to insert last insert query to the another column.For that i wish to use TRIGGERS -- -- Triggers `sync_log` -- DROP TRIGGER IF EXISTS `tg_sync_id_insertbgnn`; DELIMITER // CREATE TRIGGER ...
3
votes
1answer
1k views

Update DATE field when another field in the same table changes value. (Completely noob my google-fu didnt help.)

I'm awfully noob, so excuse my ignorance. I tried to google this but somehow i can't find an example on how to do this I hava a table, say: - id (autoinc pk) - name (varchar) - description ...
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 ...
1
vote
1answer
193 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
1answer
578 views

How can I create a TRIGGER in MySQL from this Oracle syntax?

I'm teaching myself RDMSs through a text book. I'm used to using MySQL so I'm sticking to it, but the book uses Oracle. I'm trying to create a trigger in MySQL with the Oracle command CREATE OR ...
2
votes
0answers
115 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 ...
0
votes
1answer
88 views

Execute SQL query after execute TRUNCATE in a table

I have a table called fos_user_user where I have a default record "always". Since I'm testing all the time and sometimes I need to clean that table I'm asking if there is any way to run the following ...
2
votes
0answers
53 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, ...
0
votes
1answer
57 views

Implement a deduplication trigger in Oracle

I am new in Oracle DB. We are using Toad for Oracle. I need to implement a deduplication trigger for our table. It's an alarms table and a third party vendor will be sending alarms to our database. ...
1
vote
1answer
113 views

Trigger to restrict users from creating a new table in the database

How do I create a SQL Server trigger that will restrict any users from creating a new table in the database? Please help me with this. Thanks & Regards, Ritesh.