All Questions
Tagged with trigger database-design
54 questions
0
votes
1
answer
216
views
After Insert and Update Trigger on the same table
I have the following table in MySQL:
CREATE TABLE `userlogtype` (
`UserLogTypeID` int NOT NULL AUTO_INCREMENT,
`UserLogType` varchar(150) DEFAULT NULL,
`InsertUser` varchar(100) DEFAULT NULL,
`...
1
vote
1
answer
106
views
Postgresql near real-time
Before asking the question I will update you on the situation I face.
I have physical tables in postgresql that are continuously updated by various external flows (.NET, Java, etc...), at the moment ...
1
vote
0
answers
65
views
ETL process between two Postgres Databases
I have two postgres databases, let's called them layer 1 and layer 2, that both are hosted on a separate servers. In between, there is a third server, that first, loads tables from layer 1, second, ...
- 111
0
votes
1
answer
60
views
Storing segmented time series - performance issue caused by triangle relationship / maximum search
I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a ...
- 3
0
votes
0
answers
95
views
Fastest Mechanism: Monitor new record being added to MySQL DB, and push info via WebSocket/SSE?
I have a MySQL DB on MaridDB.
There's a table that gets new records inserted at inconsistent intervals. sometimes every seconds, sometimes every few minutes.
I have a web page and instead of having ...
- 101
6
votes
2
answers
938
views
Make two columns unique both ways
I'm creating a database which stores football matches.
Each round, any given football club may only play once (home or away).
For example, after the insert of
INSERT INTO Match (Round, Home, Away) ...
- 63
1
vote
2
answers
1k
views
Disable all triggers in a database
How do I disable all triggers in a database from another database which are currently enabled .And then enable it back only the ones I disabled .
- 67
1
vote
1
answer
1k
views
Mobile finance service transaction table design
I try to create an MFS(mobile financial service/p2p money transfer, bank with limited work) for learning. The transaction looks difficult. The user can do 3 things with his account.
Deposit money to ...
4
votes
1
answer
9k
views
Trigger works for UPDATE, but for INSERT
In my database schema an organization can have multiple addresses but only one default address. I'm trying to create a trigger where if the is_default column is set to true on an insert or update, it ...
- 143
2
votes
2
answers
3k
views
how to update one column with trigger mysql
i have 2 tables the first one is
Table KEYS
game
keys
WOW
AAAAAAA-AAAAAAA-AAAAA
WOW
BBBBBBB-BBBBBBB-BBBBB
L4D2
AAAAAAA-AAAAAAA-AAAAA
TABLE inventory
game
country
language
pieces
WOW
USA
EN
0
...
- 31
2
votes
1
answer
1k
views
SQLite: Can you use a DELETE trigger to delete other records in the same table?
In this specific case I'm using a SQLite database, but I believe this to be more of a conceptual question with practical implications.
I have a nested-set model in my database and I would like to ...
- 123
1
vote
0
answers
433
views
How to log changed table fields in SQLite?
I have a table that looks like this
CREATE TABLE IF NOT EXISTS "ebooks"
(
"id" INTEGER PRIMARY KEY,
"name" TEXT NOT NULL,
"author"...
3
votes
2
answers
2k
views
What are the Cons of using triggers in place of count(*) for counting related entities?
What are the cons of using the below approach?
What steps can be taken to prevent someone from incorrectly updating the count?
Is the below approach an anti-pattern? If yes, what better approaches ...
- 195
6
votes
1
answer
10k
views
Column constraint based on values in another column
I have a PostgreSQL table phase_steps, with the following example rows:
phase_step_id|step_type|step_status|
-------------+---------+-----------+
1| RESEARCH| |
2| ...
- 167
1
vote
1
answer
2k
views
How do I return all column values for the recently inserted row/rows?
I have these two tables in a PostgreSQL 12 database:
CREATE TABLE public.test (
entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);
CREATE TABLE public.test_registration (
entity_id uuid ...
- 111
1
vote
2
answers
1k
views
Calculate stock value with PostgreSQL
I'm working on inventory system web app. It is based on latest Ruby on Rails and PostgreSQL. I need to track products stock values base on purchases and sales quantities. You can check out my ...
- 169
2
votes
2
answers
609
views
How to use tsvector_update_trigger with jsonb column?
I need to create cached ts_vector column from jsonb value. I try to create a trigger
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON tags FOR EACH ROW EXECUTE PROCEDURE
...
- 169
0
votes
1
answer
513
views
How to compare two last updated entities from two different table Postgresql?
I have 2 tables named as price and price_history. price_history table is automatically filled by triggers from price table just before updating the price table. I would like see the change at the new ...
- 1
2
votes
1
answer
438
views
How do I keep a 'running total' of purchases?
Say I have an append-only table that records purchases at a store:
create table purchases(
customer_id int,
amount numeric(10, 2) -- (in dollars)
);
There's a lot of other queries that want ...
- 199
2
votes
2
answers
878
views
SQL Server Unique Constraint on two columns with an exception
Hi all and thanks for your advice.
Expense(SupplierID(Foreign Key), DocumentID(vchar))
I understand how to add a simple unique constraint on two columns. However, if DocumentID = 'NA', I would like ...
0
votes
1
answer
132
views
How to automatically increment several Integer columns based on alphabetically ordered text columns?
Hello and thank you very much in advance for taking the time to look at my question. My question is as followed, I have a large dataset in PostgreSQL, recorded in a similar fashion as it is written in ...
- 123
0
votes
1
answer
68
views
Update main and second table with a before update trigger for message logging
I currently have the following testing schema:
CREATE TABLE userMessage (
message_id BIGSERIAL PRIMARY KEY,
creator_id BIGINT NOT NULL REFERENCES "User" (id),
created_at BIGINT ...
- 103
0
votes
3
answers
338
views
Can we prevent table from update directly but allow update using trigger from another table?
So, let's say I have 3 tables:
stocks table, it have columns: id, product_id, stock
sell table, it have columns: id, product_id, qty
buy table, it have columns: id, product_id, qty
What I want to do:...
0
votes
1
answer
32
views
Is it correct way having status or Summary columns in parent table or should choose another precedure?
My Parent Table is "Jobs" table, and there is child table that records Job's actions named "JobActions" as below:
Jobs (JobId, JobType, CurrentStage, AssignedTo)
JobActions (JobActionId, JobId, ...
- 111
4
votes
1
answer
2k
views
Complex Delete Cascade Including Self-Referencing Table
Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I ...
- 143
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
1
vote
1
answer
669
views
Ensuring unique value for group of rows in table with trigger and concurrent transactions
I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
- 123
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
0
votes
1
answer
78
views
Restrict the occurrence of a value according to the respective value in another column in a PostgreSQL table
I'm using a PostgreSQL table to link taxonomic names with the respective taxon concept as follows:
create type name_status as enum ('accepted','basionym','synonym');
create table temp (
concept_id ...
- 101
0
votes
1
answer
2k
views
Adding time slots through trigger SQL Server
I am quite new to SQL. I am now working on something to schedule appointments.
I found an existing answer about time slots, but did not quite understand.
I need to automatically add time slots to the ...
- 1
0
votes
2
answers
887
views
Advice needed regarding design of database schema keeping record of current stock
Disclaimer: I am a beginner in the field of database.
I am trying to design a database in PostgreSQL which will record the sales and purchases of items. I intend to use this database as the model ...
3
votes
2
answers
7k
views
Best practices for generating unique multi-column keys for weak entities?
How should one generate non-unique, non-natural identifiers for weak entities?
For example, if order_id is the primary key for an order table, and (order_id, item_number) is the primary key for an ...
- 190
4
votes
3
answers
2k
views
Win-Loss Record in SQL Server
I'm building a database on Microsoft SQL Server to teach myself SQL. I am a big UFC fan so I'd like to build a database on UFC fighters.
I'm building two tables fight and fighter. The fighter table ...
- 101
-2
votes
0
answers
39
views
How Can I write a trigger of DELETE for deleting any employee, as an error message. [duplicate]
The conditions are.
If the employee is not associated with any records and nothing to be printed and the employee should be deleted normally.
If the employee is associated with at least one record ...
1
vote
1
answer
986
views
Composite Primary keys and auto increment?
i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways here,...
- 111
0
votes
1
answer
876
views
Database design for a lot of rows with data archive
i'm new to database design on larger scale. I have some knowledge and experience with MySQL but now i have some performance issues.
Currently i'm using MySQL with 3 databases:
History
Snapshot
...
- 101
5
votes
2
answers
521
views
Trigger to create symmetrical rows
I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. I'...
- 161
6
votes
2
answers
779
views
Which executes first? Trigger or rule?
Using PostgreSQL.
Had a trigger which does something after delete and made a rule which stopped delete from happening if something will be true.
My question is: will the rule prevent the trigger ...
- 69
0
votes
2
answers
199
views
Elements inside a container: How to perform atomic operations on them?
In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes.
Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null).
Each element ...
- 33
6
votes
3
answers
8k
views
Prevent explicit insert into serial columns
I want to prevent explicit inserts into serial columns. I have come up to the following trigger:
drop table test_table;
create table test_table(
id bigserial primary key,
foobar text
);
create or ...
- 213
-3
votes
2
answers
4k
views
UPDATE() Function (Insert and Update Triggers)
Is there a way to have the column name dynamic in this function? For example, passing a parameter to a variable named @FldName = 'Name'. e.g. UPDATE(@FldName). Basically, what I want to do is to have ...
- 29
4
votes
1
answer
8k
views
Trigger function check if all NEW.* fields exist in another table
In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv".
...
- 393
2
votes
1
answer
3k
views
MySQL created_by or updated_last-by [closed]
I've been giving the task of taking SQL DDL from data modeler and to transcribe it into mySQL for an internship. The only thing I cannot figure out is how to take
Created_By VARCHAR2(30) DEFAULT USER,...
- 141
1
vote
2
answers
72
views
Schema/Structure to handle secondary computed data
I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play.
One of the features of our league is that we convert raw scores to ...
- 111
1
vote
1
answer
1k
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 ...
- 13
1
vote
0
answers
39
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 NULL,...
- 11
14
votes
1
answer
2k
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 ...
- 273
0
votes
2
answers
2k
views
SQL Server 2012. Update second table based on input from first table
I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
2
votes
1
answer
231
views
What are the risks for logging across databases via a trigger?
I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here.
On SQL Server 2005, we currently log certain table changes via triggers using ...
- 121
1
vote
2
answers
570
views
Error in creating trigger
I have 4 tables:
event:
event_id(p.k) | uid | circle_id(f.k)
activity:
uid | performed_activity_id(f.k->event_id) | activity_type_id
follow:
follower_id | circle_id(f.k)
notification:
sender_id ...
- 111