Hot answers tagged

5

A side effect of this command is that you take an exclusive lock on the table: ALTER TABLE DISABLE trigger ALL; The manual on the DISABLE TRIGGER clause: This command acquires a SHARE ROW EXCLUSIVE lock. About SHARE ROW EXCLUSIVE: [...] This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it ...


5

As stated in the manual Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp. (...


4

Is there any way I can obtain inside a trigger the name of the table on which the trigger is defined? No, there is no such capability in any of the current (as of year 2020) Db2 versions, for reasons that I won't go into. For a comparison, consider why a C function cannot know its name or the name of the function that called it. For each trigger that you ...


4

You don't. You will need to write your own trigger function. The built in one only works for text columns, not for jsonb columns and not for functions over columns. On newer versions, an alternative would be use a generated column. create table tags ( translations jsonb, tsv tsvector generated always as (to_tsvector('pg_catalog.english',...


4

bcp has an option : FIRE_TRIGGERS link here (By default, triggers are not fired. To fire triggers explicitly, use the -h option with the FIRE_TRIGGERS hint.) -h"FIRE_TRIGGERS" FIRE_TRIGGERS Specified with the in argument, any insert triggers defined on the destination table will run during the bulk-copy operation. If FIRE_TRIGGERS is not ...


3

This is precisely what foreign key constraints were invented for. You'd want to create a foreign key on the appointments table's field that references the primary key field of the patient table. This will enforce that the person is a patient before there can be an appointment created for them. Note that a foreign key constraint will likely be more performant ...


3

It depends on if you use an INSTEAD OF trigger or an AFTER trigger. It sounds like you want an INSTEAD OF trigger where you can prevent the default values from being INSERTed or UPDATEd and replace them with NULLs instead. (An AFTER trigger would effectively cause the index to update twice.) You can read more on how to implement an INSTEAD OF trigger here.


3

You can add a trigger to your python script CREATE TABLE IF NOT EXISTS sum (id TEXT UNIQUE, name TEXT, total TEXT ) CREATE TABLE IF NOT EXISTS newtable (id TEXT, name TEXT, num TEXT ) INSERT INTO sum(id, name, total) VALUES('001', 'name1', ''); INSERT INTO sum(id, name, total) VALUES('002', '...


3

As far as I'm aware this is not possible with the triggers feature of Azure / SQL Server. One reason likely being, as you mentioned, it breaks the Consistency property of the ACID principals of a database: ...any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. ...


3

I should think a table-level CHECK constraint would serve here, something like CONSTRAINT c1_c2_null_match CHECK ((c1 IS NULL AND c2 IS NULL) OR (c1 IS NOT NULL AND c2 IS NOT NULL)) See the MySQL 8.0 documentation. You could instead use a trigger, but can run into consistency issues that way. Certain bulk-load operations will bypass triggers, ...


3

I did not follow the exact details of your data model, but a deferred constraint trigger is always subject to race conditions unless you operate with the SERIALIZABLE transaction isolation level. The reason is that concurrent updates of config could cause the trigger function to run in parallel in two sessions, where they cannot see the effects of the other ...


3

The general idea in this solution is to add a little denormalization so the desired restrictions can all be enforced with regular foreign keys, check constraints, and filtered unique indexes. I don't know Postgres well enough, so this is a SQL Server implementation. The main features are: Current revision moved from config to revisions Current revision is ...


3

For the username, there are a bunch of built-in functions that will return the current user name. They are not all the same, so take your pick - I typically use SUSER_SNAME(). For the command, if you were on a supported and non-antique version (even SQL Server 2014 would do), you could use sys.dm_exec_input_buffer to tell you the last batch sent by the ...


2

Since there's been some ongoing interest to this and years later I've become quite well acquainted with all of the above, here's a short summary in performance terms: I did a test on some version of SQL Server 2016 involving inserting, updating, and deleting 10000 rows from 40 different types of tables one by one, and charted the overall time spent, basic ...


2

It cannot be done. From the docs: The arguments are literal string constants


2

Your trigger's broken, but it's not to blame. SQL Server has Statement Triggers, not Row Triggers, so it should be CREATE TRIGGER [dbo].[trg_users_modify_date] ON [dbo].[users] FOR INSERT,UPDATE AS BEGIN UPDATE users SET last_modify_date = dbo.fn_GetSystemDate() WHERE id in ( select id FROM INSERTED); END This deadlock is between a PK index key ...


2

Double-quote identifiers automatically where needed with format() (like Laurenz demonstrates), or quote_ident(). See: Are PostgreSQL column names case-sensitive? SQL injection in Postgres functions vs prepared queries Also, your trigger function can be more efficient like this: CREATE OR REPLACE FUNCTION my_latlon_function() RETURNS trigger LANGUAGE ...


2

Use format with the %I placeholder for identifiers: EXECUTE format('select $1.%I', latcolumn) USING NEW INTO lat;


2

If the numbers have to be accurate, the trigger solution is the best one. To prevent the application from messing with the summary table directly, have that table owned by a different user than the one the application uses to connect. Grant only the SELECT privilege to the application user.


2

CREATE TRIGGER AFTER INSERT ON newtable FOR EACH ROW BEGIN UPDATE sum SET total = total + NEW.num WHERE name = NEW.name; END DEMO


2

Is is preferred that you post a full repro. I tried to repro this and below code returned "ALTER TABLE" and not the text you posted. My guess is that you use some tool to modify the table and this tool starts by changing lock escalation before doing the real stuff. Just a guess, though, since we don't have a repro. Anyhow, here's a repro using your ...


2

That's the way materialized views work. They essentially create a copy of the results of the query in the view. The base table(s) used in that query are automatically updated when the base table changes. That is implemented via a trigger on the base table that records all changes into a materialized view log associated to that base table. A subsequent ...


2

I can't reprocuce your behaviour. CREATE TABLE bookings(ID int AUTO_INCREMENT PRIMARY KEY,unique_id int, branch_id int, pos_user_id int, user_id int, user_address_id int) CREATE TABLE bookings_clone(ID int ,unique_id int, branch_id int, pos_user_id int, user_id int, user_address_id int) CREATE TRIGGER after_booking_inserted AFTER INSERT ON bookings FOR ...


2

Most database systems normally handle this with a Check Constraint, but your version of MySQL doesn't have that implemented. So you can implement it with a Trigger as discussed in this StackOverflow answer.


2

You would have to use the pure string representation of the array. CREATE TRIGGER on_create_flag_special BEFORE INSERT ON example_table FOR EACH ROW EXECUTE PROCEDURE example_function( '{magicname1,magicname2}', '{user1,user2}' );


2

All Triggers fire within the scope of the same Transaction that the INSERT statement that generated them runs in. Therefore if the Transaction of the INSERT statement you executed ran to completion, then so did the After Insert Trigger that fires from that INSERT statement. Here's some straight to the point information and tests that prove this out: So in ...


2

Why? Because no one implemented it. My guess is that since statement level triggers have been implemented recently, this feature was left for a later stage.


2

CDC and temporal doesn't say who did the modification. Audit has lower overhead than triggers. Audit uses extended events under the covers. Audit might not give you which rows where read or modified (when parameterized sql is submitted), but that wasn't a prerequisite. I. e, I'd look at Server Audit first.


2

On the Hangfire website I couldn't find any reference that it supports outside modifications of the SQL database. So I would assume that it is not supported. When you modify database directly, you are skipping all caching and validation layers implemented in the application. It may lead to unstable application and it is very hard to debug. Also, database ...


2

The main problem with your trigger is that it's oblivious to concurrent changes. You requested an example in the comments so here's a simple one. Starting from the dbfiddle with 'lisa' being CEO of 'amd', let's add a second CEO: insert into staff values (alice', 'amd'); insert into roles values (2, 'ceo', 'alice'); tst=> select * from roles; id | role | ...


Only top voted, non community-wiki answers of a minimum length are eligible