I have two tables A and B. In one transaction I update both tables. In the trigger function after update I want to select data from both tables, but the result is the old data. The trigger is on table A, but they both are being updated in the same transaction. Is this possible to achieve or I have to think for another approach?
Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger.
Here’s a simplified example of what I’m trying to do, which does its job fine, but inefficiently:
DROP TRIGGER IF EXISTS update_second_table; DELIMITER // CREATE TRIGGER update_second_table BEFORE UPDATE ON first_table FOR EACH ROW BEGIN /* putting IF statements on one line so it's easier to see what's happening */ IF NOT(OLD.firstname <=> NEW.firstname) THEN UPDATE second_table SET firstname = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.middlename <=> NEW.middlename) THEN UPDATE second_table SET middlename = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.lastname <=> NEW.lastname) THEN UPDATE second_table SET lastname = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.nickname <=> NEW.nickname) THEN UPDATE second_table SET nickname = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.dob <=> NEW.dob) THEN UPDATE second_table SET dob = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.email <=> NEW.email) THEN UPDATE second_table SET email = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.address <=> NEW.address) THEN UPDATE second_table SET address = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.city <=> NEW.city) THEN UPDATE second_table SET city = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.state <=> NEW.state) THEN UPDATE second_table SET state = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.zip <=> NEW.zip) THEN UPDATE second_table SET zip = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; IF NOT(OLD.phone <=> NEW.phone) THEN UPDATE second_table SET phone = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; END; // DELIMITER;
As you can see, depending on how many columns are updated in `first_table`, there can be as many as 11 update statements on the same row in `second_table`.
Is there any way to combine the update statements into one?
Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback.
I’d like to update a column (village_id) every time a new row is entered or updated with the primary key value of a different table (which is not connected as a foreign key directly). See below for an outline of my tables and trigger + function.
Unfortunately, after using the code below I received an error each time I was entering a new row with information. I used an UPDATE statement, but I also considered using an insert into statement, but wasn’t sure what is more preferable. Thank you in advance.
CREATE TABLE register_table ( register_id integer CONSTRAINT register_id_pk PRIMARY KEY, village_id integer NOT NULL ); CREATE TABLE er_table ( er_id integer CONSTRAINT er_id_pk PRIMARY KEY, register_id integer NOT NULL, village_id integer NOT NULL ); CREATE OR REPLACE FUNCTION update_village_id_column() RETURNS trigger AS $ BODY$ BEGIN UPDATE schema_example.er_table SET village_id = register_table.village_id FROM schema_example.register_table WHERE (schema_example.register_id = new.register_id); RETURN new; END; $ BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER update_village_id AFTER INSERT OR UPDATE ON schema_example.er_table FOR EACH ROW EXECUTE PROCEDURE update_village_id_column();
Say a rogue is fighting an enemy, and either they or someone else has unseen servant and they sent it to engage that enemy. Can the rogue now use a sneak attack?
Unseen servant is very clearly described as an entity that shouldn’t be used or useful in combat, this seems to go against that idea. In the same vein, rogues get sneak attack when there are others supporting them, again, the idea of them (if multiclassing) using a spell for this seems to be more of a loophole than a valid strategy.
The Path of the Zealot barbarian (Xanathar’s Guide to Everything, p. 11) gets the Divine Fury feature at level 3, which states:
At 3rd level, while you’re raging, the first creature you hit on each of your turns with a weapon attack takes extra damage equal to 1d6 + half your barbarian level. The extra damage is necrotic or radiant; you choose the type of damage when you gain this feature.
Does it work like the Rogue’s Sneak attack, in that it can trigger on opportunity attacks, since it’s a different turn? Or does the "on each of your turns" bit make it not work.
Does spending movement to stand up from prone trigger the secondary damage effect of the booming blade spell from the Sword Coast Adventurer’s Guide (p. 142)? The spell says:
If the target willingly moves before the duration expires, it immediately takes 1d8 thunder damage, and the spell ends.
In D&D 5e using a wizard of the School of Necromancy:
If I cast bestow curse on a monster, then kill it with a crossbow, would it trigger the Grim Harvest feature due to the extra 1d8 damage? Does it matter if the monster had 1 HP?
What about if I cast bestow curse, then hit it with magic missile (and kill it with just 1 missile) – would Grim Harvest trigger off the missile or the curse?
If I cast Bestow Curse on a monster then shoot it with a crossbow, would it trigger the Grim Harvest ability due to the extra d8 of damage? Does it matter if the monster had 1 hp?
What about if I cast Bestow Curse then hit it with Magic Missile, would Grim Harvest trigger off the missile or the curse?
I have two database say db1 and db2. And two tables user_auth_table in db1 and user_table in db2. Now I want to trigger a function which sync up few values in both tables for each new row or on each new insertion in user_auth_table.
I have tried to write on my own and creating trigger in db1. But the error says user_table doesn’t exist. Which I know why I am getting this error. Any suggestion to target the tables outside the db ?
CREATE TRIGGER sync_user_table AFTER INSERT ON user_auth_table for each row execute procedure syncAndMaintainUsersTable() create or replace function syncAndMaintainUsersTable() returns trigger as $ BODY$ begin if NEW.user_uuid<>OLD.user_uuid then insert into user_table values (NEW.user_uuid, CURRENT_TIMESTAMP, NEW.provider); end if; return new; end; $ BODY$ language plpgsql;
If a Succubus charms a humanoid with the Charm action, and then commands the humanoid to do something suicidal, the target gets to roll a saving throw. Succeeding means they do not perform the suicidal command – it interrupts the command.
But if they fail the saving throw, they go through with the action and take the damage, but should this then result in another saving throw?
If the target suffers any harm or receives a suicidal Command, it can repeat the saving throw, ending the effect on a success.
My interpretation would be that the two are different triggers, even if they happen during the same round. The Succubus gives a suicidal command, the humanoid rolls a saving throw, fail, and then they do the action when it is their turn, taking damage and rolling again.
If not we end up with the awkward situation where falling off a 1000-foot cliff doesn’t even give you a chance to save against being charmed, just because you already failed the save for the command itself.