i am trying to create trigger with mysql2,express and nodejs in my database and is there a way to create trigger in database table without sequalize ,delimiter is not working

        var sqlBeforeUpdateTrigger=`CREATE TRIGGER  beforeSupplierUpdate
        BEFORE UPDATE ON Supplier
        FOR EACH ROW
        BEGIN
        INSERT INTO SupplierUpdate
        SET action = 'update',
        supplierId=old.supplierId,
        name=old.name,
        email=old.email,
        country=old.country,
        state=old.state,
        city=old.city,
        street=old.street,
        pinCode=old.pinCode,
        poBox=old.poBox,
        supplierDetails=old.supplierDetails,
        updatedOn= NOW();
        END;`;

    
        return new Promise(async (resolve, reject) => {
       
            db.query(sqlBeforeUpdateTrigger, function (err, result, fields) {
               if (err) {
                  reject(err);
               }
              resolve(result);
             });
             
           });

CodePudding user response：

The problem is delimiter is only supported by mysql client provided by workbench or some other softwares inorder to run procedures where we have to seperate instruction instead of delimiter we can join different instruction using javascript operator to get sql query string i.e

var sqlQueryTrigger='CREATE TRIGGER beforeSupplierUpdate' 
  ' BEFORE UPDATE ON Supplier'  
  ' FOR EACH ROW'  
     ' BEGIN'  
  ' INSERT INTO SupplierUpdate' 
  'SETaction="update",supplierId=old.supplierId,name=old.name,
  email=old.email,country=old.country,state=old.state,
  city=old.city,street=old.street,
  pinCode=old.pinCode,poBox=old.poBox,
  supplierDetails=old.supplierDetails,
  updatedOn= NOW();'  
 'END;';

    return new Promise(async (resolve, reject) => {
   
        db.query(sqlBeforeUpdateTrigger, function (err, result, fields) {
           if (err) {
              reject(err);
           }
          resolve(result);
         });
         
       });

CodePudding user response：

for starters, your query is invalid. You're mixing the INSERT and UPDATE statements. When you do an update, the general syntax looks like UPDATE table_name SET column=value, but that's not the case when you insert. The syntax for that is INSERT table_name (column1, column2, column3) VALUES (val_for_column1, val_for_column2, val_for_column3)