Closed AnneliO closed 2 months ago
Nappasin vielä edellisen versionvaihdon vastaavasta tiketistä näiden triggereiden rimpsut
delimiter $$
create or replace trigger before_message_preferences_insert
before insert on borrower_message_preferences
for each row
begin
if new.message_attribute_id=1 or new.message_attribute_id=2 then
set new.wants_digest=1;
end if;
end $$
delimiter ;
create or replace trigger before_borrowers_insert
BEFORE INSERT
ON borrowers FOR EACH ROW
SET new.userid=new.cardnumber;
create or replace trigger before_borrowers_update
BEFORE UPDATE
ON borrowers FOR EACH ROW
SET new.userid=new.cardnumber;
DELIMITER //
CREATE OR REPLACE TRIGGER insert_biblioitems_kd5270
BEFORE INSERT ON biblioitems
FOR EACH ROW FOLLOWS insert_remove_hyphens
BEGIN
IF NEW.isbn IS NOT NULL AND NEW.isbn like '%|%' THEN SET NEW.isbn = TRIM(SUBSTRING_INDEX(NEW.isbn, '|', 1));
END IF;
IF NEW.ean IS NOT NULL AND NEW.ean like '%|%' THEN SET NEW.ean = TRIM(SUBSTRING_INDEX(NEW.ean, '|', 1));
END IF;
IF NEW.issn IS NOT NULL AND NEW.issn like '%|%' THEN SET NEW.issn = TRIM(SUBSTRING_INDEX(NEW.issn, '|', 1));
END IF;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE TRIGGER update_biblioitems_kd5270
BEFORE UPDATE ON biblioitems
FOR EACH ROW FOLLOWS update_remove_hyphens
BEGIN
IF NEW.isbn IS NOT NULL AND NEW.isbn like '%|%' THEN SET NEW.isbn = TRIM(SUBSTRING_INDEX(NEW.isbn, '|', 1));
END IF;
IF NEW.ean IS NOT NULL AND NEW.ean like '%|%' THEN SET NEW.ean = TRIM(SUBSTRING_INDEX(NEW.ean, '|', 1));
END IF;
IF NEW.issn IS NOT NULL AND NEW.issn like '%|%' THEN SET NEW.issn = TRIM(SUBSTRING_INDEX(NEW.issn, '|', 1));
END IF;
END //
DELIMITER ;
delimiter $$
create or replace trigger insert_remove_hyphens
before insert on biblioitems
for each row
begin
set new.isbn = REPLACE(new.isbn,'-',''), new.ean = REPLACE(new.ean,'-','');
end$$
delimiter ;
delimiter $$
create or replace trigger update_remove_hyphens
before update on biblioitems
for each row
begin
set new.isbn = REPLACE(new.isbn,'-',''), new.ean = REPLACE(new.ean,'-','');
end$$
delimiter ;
delimiter $$
create or replace trigger before_items_insert
before insert on items
for each row
begin
set NEW.permanent_location=NEW.location;
end$$
delimiter ;
delimiter $$
create or replace trigger before_items_update
before update on items
for each row
begin
set NEW.permanent_location=NEW.location;
end$$
delimiter ;
DELIMITER $$
CREATE TRIGGER insert_smsalertnumber
BEFORE INSERT
ON borrowers FOR EACH ROW
BEGIN
set new.smsalertnumber=new.mobile;
END$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER update_smsalertnumber
BEFORE UPDATE
ON borrowers FOR EACH ROW
BEGIN
set new.smsalertnumber=new.mobile;
END$$
DELIMITER ;
PÄIVITETTY mobile<>sms-triggeri updatelle:
DELIMITER $$
CREATE OR REPLACE TRIGGER update_smsalertnumber
BEFORE UPDATE
ON borrowers FOR EACH ROW
BEGIN
if (new.smsalertnumber <=> old.smsalertnumber or (new.smsalertnumber is null and old.smsalertnumber <=> '') or (new.smsalertnumber <=> '' and old.smsalertnumber is null)) and not (new.mobile <=> old.mobile or (new.mobile is null and old.mobile <=> '') or (new.mobile <=> '' and old.mobile is null)) then set new.smsalertnumber=new.mobile; end if;
if (new.mobile <=> old.mobile or (new.mobile is null and old.mobile <=> '') or (new.mobile <=> '' and old.mobile is null)) and not (new.smsalertnumber <=> old.smsalertnumber or (new.smsalertnumber is null and old.smsalertnumber <=> '') or (new.smsalertnumber <=> '' and old.smsalertnumber is null)) then set new.mobile=new.smsalertnumber; end if;
END$$
DELIMITER ;
Nämä olikin laitettu jo muille kimpoille paitsi OUTIlle.
Sain kaikki muut triggerit laitettua paitsi before_message_preferences_update.
Nexteille on myös päivitetty before_borrowers -triggerit käyttämään SET new.userid=new.cardnumber, new.password_expiration_date=new.dateexpiry password expiration datejen päivittämistä varten https://github.com/KohaSuomi/Koha-23x/issues/98#issuecomment-1988496049.
Myös before_message_preferences_update lisätty outi-nextille seuraavasti:
delimiter $$
create or replace trigger before_message_preferences_update
before insert on borrower_message_preferences
for each row
begin
if new.message_attribute_id=1 or new.message_attribute_id=2 then
set new.wants_digest=1;
end if;
end $$
delimiter ;
Siirrettävä ominaisuus
Meillä on käytössä erilaisia tietokantatriggereitä, joiden toiminta nexteillä pitää varmistaa.
Olemassa on seuraavat triggerit
Asiasanat/tagit
triggeri, triggerit, trigger, triggers