Hot answers tagged

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

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, ...


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.


1

You'd have to schedule a program to run regularly and process the table. Usually it is better to implement such logic in the SELECT statements.


1

In MySQL you need to make deferent trigger for updating and Inserting; DELIMITER $$ CREATE TRIGGER before_ITEM _insert BEFORE INSERT ON ITEM FOR EACH ROW BEGIN SET NEW.Total = NEW.Quantity * NEW.Price; END $$ DELIMITER ; CREATE TRIGGER before_ITEM _UPDATE BEFORE UPDATE ON ITEM FOR EACH ROW BEGIN SET NEW.Total = NEW.Quantity * NEW.Price; END $$ ...


1

string_to_array() in your solution is only useful if you want to allow non-standard array syntax. Else it's simpler and cheaper to just cast. And you can do the assignment at declaration time. Like: CREATE OR REPLACE FUNCTION example_function() RETURNS TRIGGER LANGUAGE plpgsql AS $func$ DECLARE special_names text[] := TG_ARGV[0]::text[]; ...


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