CODEWITHSUNDEEP
sqlitetriggers
nwind
nwind

Reputation: 89

Sqlite3 two conditions in one trigger for initcap

I would like to ask you for a help. I have two trigger after insert, which check the insert of name and surname. If the name or surname start with small letter, trigger change name and surname to right format. Is it possible to make one trigger from this two triggers?

CREATE TRIGGER check_first_letter1
AFTER INSERT ON MEMBERS
WHEN (SUBSTR(NEW.name,1,1) = LOWER(SUBSTR(NEW.name,1,1)))  BEGIN
    UPDATE MEMBERS SET NAME=(select (UPPER(substr(NEW.name,1,1))||LOWER(substr(NEW.name,2,10)))) WHERE ID=NEW.ID;
    END;


CREATE TRIGGER check_first_letter2
AFTER INSERT ON MEMBERS
WHEN (SUBSTR(NEW.surname,1,1) = LOWER(SUBSTR(NEW.surname,1,1)))  BEGIN
    UPDATE MEMBERS SET SURNAME=(select (UPPER(substr(NEW.surname,1,1))||LOWER(substr(NEW.surname,2,10)))) WHERE ID=NEW.ID;
    END;

Upvotes: 0

Views: 154

Answers (1)

MikeT
MikeT

Reputation: 56953

I believe the following will do what you wish :-

CREATE TRIGGER check_first_letter1
AFTER INSERT ON MEMBERS
BEGIN
    UPDATE MEMBERS SET NAME=(select (UPPER(substr(NEW.name,1,1))||LOWER(substr(NEW.name,2,10)))) 
       WHERE ID=NEW.ID AND (SUBSTR(NEW.name,1,1) = LOWER(SUBSTR(NEW.name,1,1)));
    UPDATE MEMBERS SET SURNAME=(select (UPPER(substr(NEW.surname,1,1))||LOWER(substr(NEW.surname,2,10)))) 
        WHERE ID=NEW.ID AND (SUBSTR(NEW.surname,1,1) = LOWER(SUBSTR(NEW.surname,1,1)));
END

i.e.

  • if fred and Bloggs is inserted the Trigger changes them to Fred and Bloggs.
  • if Fred and bloggs is inserted the Trigger changes them to Fred and Bloggs.
  • if fred bloggs is inserted the Trigger changes them to Fred and Bloggs.

However, the following simpler trigger would do the same :-

CREATE TRIGGER check_first_letter_always
AFTER INSERT ON MEMBERS
BEGIN
    UPDATE MEMBERS SET  
            NAME=(select (UPPER(substr(NEW.name,1,1))||LOWER(substr(NEW.name,2,10)))),
            SURNAME=(select (UPPER(substr(NEW.surname,1,1))||LOWER(substr(NEW.surname,2,10))))
        WHERE ID=NEW.ID;
END;

Upvotes: 1

Related Questions