All Questions
Tagged with trigger foreign-key
26 questions
0
votes
1
answer
58
views
how to write trigger to simulate before update to set foreign key field to null upon violation
I have two databases that synchronize with each other. One database is a full set of data, the other is a limited dataset. This means that the limited database sometimes doesn't have the data the ...
- 11
0
votes
1
answer
32
views
Create composed key (YEAR+COUNT) field with trigger after insert in SQLITE
I have a table Product with two columns: name of product and price
CREATE TABLE IF NOT EXISTS YearCounters (
year INTEGER PRIMARY KEY,
counter INTEGER DEFAULT 0
);
-- Product Table
CREATE ...
0
votes
1
answer
47
views
Postgres: automatic action (increment count of foreign key) after update
How to automate an update in a table, after a change in another table ?
Say (please see below) I have a table countries, and a table cities. How to make that, when adding a city in table cities, the ...
- 3
0
votes
3
answers
264
views
How to change insert values from 0 to NULL for a specific field in order to avoid foreign key violation?
I have a foreign key linking INSTRUMENT.VENDOR to VENDOR.NAME.
Unfortunately the program where users enter information is attempting to insert into INSTRUMENT with a value of 0 for vendor. I need to ...
- 113
1
vote
1
answer
7k
views
How to disable FOREIGN KEY referential integrity trigger in PostgreSQL without disabling other triggers in table?
FOREIGN KEYs referential integrity in PostgreSQL can be disabled by executing:
ALTER TABLE schema.my_table
DISABLE TRIGGER ALL;
But unfortunately the command will also disable all other triggers ...
-1
votes
1
answer
149
views
How to Link newly inserted row with Foreign Key?
In my database I have 2 tables linked together with a foreign key relationship.
Parent Table = controls.slot_table
Child Table = controls.IO_table
When a new row is inserted into the parent table (A), ...
- 3
0
votes
0
answers
71
views
How do I automatically insert a row into parent table when a row is created in a child table?
I am working on a MySQL database that has to treat organizations and people as interchangeable in some instances and not others. Because of this, I created a table called LegalEntity as a superclass. ...
0
votes
0
answers
1k
views
Foreign key cascade & triggers
I have a question on how to elaborate a schema in PostgreSQL which involves:
A trigger function used to keep a certain consistency calculation in a table;
A foreign key whose value in the original ...
- 101
0
votes
1
answer
1k
views
Oracle: "table is mutating, trigger/function may not see it"
(Yes, this is one of those question again...)
I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server.
I have a table I1 (called t_link in my ...
- 239
5
votes
1
answer
3k
views
Disabling triggers makes DELETE fast, but the table has no triggers?
I am deleting 130k out of 750k rows from a PostgreSQL table.
The first time, it took 8 hours to complete the delete query.
Second time, I added an index to the table, and rebuilt that index. Now it ...
5
votes
2
answers
10k
views
Solving ON DELETE CASCADE cycles with triggers on MS SQL Server
I have code that is working fine in PostgreSQL and I now have to port it to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it:
-- ...
- 239
1
vote
1
answer
38
views
two-table-over <+> functional relationship: maintaining referential integrity
I have three tables, the last two having a many to one relationship with the first, written here in SQL:
create table study (
id bigint primary key,
name text
);
create table treatment (
...
- 113
0
votes
1
answer
128
views
How to deal with refrencing a foreign key to two tables?
I have an interesting situation where I am making a data base off someone else's design that has to be as rule complaint as possible.
The issue I have is with the section shown above as it is a sub-...
- 1
0
votes
1
answer
2k
views
MYSQL - Error in After Insert trigger affects the actual insert on the trigger table
I have two tables call it - table_1 and table_2
I have an After insert trigger on table_1 and in the trigger I insert on table_2 which has some fk constraints on another table_3.
When the insert on ...
- 103
0
votes
1
answer
1k
views
how can i write a trigger that compare between two tables in phpmyadmin mysql [closed]
I have two tables that has rows that have same values.
I want to write a trigger that will check for mypin in debtors tables and if mypin exists in debtors it fills the debtor row in the users ...
1
vote
2
answers
122
views
Alternative to second column for tracking a FOREIGN KEY pre-deletion
Let's say I have a table A, that has one column: just an ID. This is obviously the primary key.
Next, I have a second table, which has an ID for table A, and then a second ID. The primary key is on ...
- 1,523
1
vote
1
answer
1k
views
Is disabling triggers to disable foreign key constraints a good idea?
Background
We're moving a legacy application from a non-relational database to PostgreSQL.
Problem
We'd like to enable foreign keys on a lot of fields for future data integrity and so that ...
- 121
1
vote
1
answer
163
views
Mysql auto insert record in primary table if not exist
Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key?
For example, assume these tables:
- user(id, name, age)
- topic(id, name)
- ...
- 111
12
votes
2
answers
2k
views
How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?
I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t ....
Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, ...
- 353
2
votes
2
answers
8k
views
insert inserted id to another table
Here's the scenario:
create table a (
id serial primary key,
val text
);
create table b (
id serial primary key,
a_id integer references a(id)
);
create rule a_inserted as on insert to a do ...
- 257
2
votes
0
answers
1k
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 ...
- 143
2
votes
1
answer
620
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 ...
- 123
1
vote
2
answers
8k
views
Foreign key with multiple references
I have the following three tables in mysql database named "THE_COLLEGE"
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | ...
- 63
3
votes
1
answer
4k
views
Is there a workaround for lack of support of cascade triggers with MySQL?
As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a ...
- 349
3
votes
2
answers
29k
views
How to make a trigger that will compare input with a value of other table?
I had made two tables with the following fields:
TABLE 1: "Bookings"
FIELD: "fk_flight_number"
FIELD: "date_of_reservation"
TABLE 2: "Flights"
FIELD: "flight_number"
FIELD: "...
0
votes
1
answer
172
views
Derived value not calculating as expected using combination of FK and Triggers (MySQL)
I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints.
There are 3 tables involved in this arrangement
responses
------------------------
...
- 165