mysql 如何在SEQUELIZE(nodeJS)中创建触发器？

4jb9z9bj 于 2023-05-16 发布在 Mysql

关注(0)|答案(3)|浏览(156)

我正在尝试使用sequelize创建触发器。主要思想是在创建USER之后创建CONFIG的示例。

// USER MODEL
module.exports = function(sequelize, DataTypes) {    
    var User = sequelize.define('User', {
        name        : DataTypes.STRING(255),
        email       : DataTypes.STRING(255),
        username    : DataTypes.STRING(45),
        password    : DataTypes.STRING(100),
    }, {
        classMethods : {
            associate : function(models) {
                User.hasOne(models.Config)
            }
        }
    });    
    return User;
};

// CONFIG MODEL
module.exports = function(sequelize, DataTypes) {
    var Config = sequelize.define('Config', {
        notifications   : DataTypes.INTEGER
    }, {
        classMethods : {
            associate : function(models) {
                Config.belongsTo(models.User)
            }
        }
    });

    return Config;
};

正如您所看到的，一个“user”有一个“config”，而一个“config”属于一个“user”，所以在创建一个用户之后，我想自动创建他的配置行。
目标是：

DELIMITER //
CREATE TRIGGER create_config AFTER INSERT ON user
  FOR EACH ROW
BEGIN
    insert into config    (user_id)     values(new.user_id);
END; //
DELIMITER ;

现在，我要做的模拟是：

.then(function(user){
   return dao.Config.create(req.body, user, t);
})

一旦创建了一个用户，我就像这样创建他的配置...它工作，但不是我正在寻找的。
我该怎么做？

mysql

来源：https://stackoverflow.com/questions/29716346/how-to-create-a-trigger-in-sequelize-nodejs

3条答案

按热度按时间

avwztpqn1#

您可以通过以下两种方式之一来完成此操作。正如您所指出的，您可以在数据库本身中创建触发器。您可以运行原始序列化查询来完成此操作：

sequelize.query('CREATE TRIGGER create_config AFTER INSERT ON users' +
  ' FOR EACH ROW' +
  ' BEGIN' +
  ' insert into configs (UserId) values(new.id);' +
  'END;')

或者，您可以在用户模型上创建一个hook，它对afterCreate事件执行操作：

module.exports = function(sequelize, DataTypes) {    
  var User = sequelize.define('User', {
    name        : DataTypes.STRING(255),
    email       : DataTypes.STRING(255),
    username    : DataTypes.STRING(45),
    password    : DataTypes.STRING(100),
  }, {
    classMethods : {
      associate : function(models) {
        User.hasOne(models.Config)
      }
    },
    hooks: {
      afterCreate: function(user, options) {
        models.Config.create({
          UserId: user.id
        })
      }
    }
  });
  return User;
};

赞(0）回复(0）举报 2023-05-16

vjhs03f72#

SQL TRIGGER helper同时支持PostgreSQL和SQLite

用法：将此放在.sync之后：

await sequelize.sync()
await createTrigger(sequelize, Post, 'insert', `UPDATE "${User.tableName}" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId"`)
await createTrigger(sequelize, Post, 'delete', `UPDATE "${User.tableName}" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId"`)
await createTrigger(
  sequelize,
  Post,
  'update',
  `UPDATE "${User.tableName}" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
UPDATE "${User.tableName}" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId"`,
  {
    when: 'OLD."UserId" <> NEW."UserId"',
  }
)

触发器每次都被重新定义，因此即使它们之前已经定义，也可以工作，即不需要sync({ force：How does sequelize.sync() work, specifically the force option?
助手：

// on: lowercase 'insert', 'delete' or 'update'
async function createTrigger(sequelize, model, on, action, { after, when, nameExtra } = {}) {
  if (after === undefined) {
    after = 'AFTER'
  }
  if (nameExtra) {
    nameExtra = `_${nameExtra})`
  } else {
    nameExtra = ''
  }
  const triggerName = `${model.tableName}_${on}${nameExtra}`
  if (when) {
    when = `\n  WHEN (${when})`
  } else {
    when = ''
  }
  if (sequelize.options.dialect === 'postgres') {
    const functionName = `${triggerName}_fn`
    await sequelize.query(`CREATE OR REPLACE FUNCTION ${functionName}()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  ${action};
  RETURN NEW;
END;
$$
`)
    // CREATE OR REPLACE TRIGGER was only added on postgresql 14 so let's be a bit more portable for now:
    // https://stackoverflow.com/questions/35927365/create-or-replace-trigger-postgres
    await sequelize.query(`DROP TRIGGER IF EXISTS ${triggerName} ON "${model.tableName}"`)
    await sequelize.query(`CREATE TRIGGER ${triggerName}
  ${after} ${on.toUpperCase()}
  ON "${model.tableName}"
  FOR EACH ROW${when}
  EXECUTE PROCEDURE ${functionName}();
`)
  } else if (sequelize.options.dialect === 'sqlite') {
    await sequelize.query(`
CREATE TRIGGER IF NOT EXISTS ${triggerName}
  ${after} ${on.toUpperCase()}
  ON "${model.tableName}"
  FOR EACH ROW${when}
  BEGIN
    ${action};
  END;
`)
  }
}

我认为每个DBMS不可能避免大的if，因为：

SQLite没有存储过程：Creating stored procedure in SQLite
PostgreSQL需要一个用于触发器的过程：is it possible to create trigger without execute procedure in postgresql?

下面是一个带有Assert的最小可运行示例。在本例中，我们使用一个触发器来保持每个用户的User.postCount值在用户创建或删除某些帖子时保持最新。
main.js

const path = require('path')
const { DataTypes } = require('sequelize')
const common = require('./common')
const sequelize = common.sequelize(__filename, process.argv[2])
const force = process.argv.length <= 3 || process.argv[3] !== '0'
;(async () => {

// on: lowercase 'insert', 'delete' or 'update'
async function createTrigger(sequelize, model, on, action, { after, when, nameExtra } = {}) {
  if (after === undefined) {
    after = 'AFTER'
  }
  if (nameExtra) {
    nameExtra = `_${nameExtra})`
  } else {
    nameExtra = ''
  }
  const triggerName = `${model.tableName}_${on}${nameExtra}`
  if (when) {
    when = `\n  WHEN (${when})`
  } else {
    when = ''
  }
  if (sequelize.options.dialect === 'postgres') {
    const functionName = `${triggerName}_fn`
    await sequelize.query(`CREATE OR REPLACE FUNCTION ${functionName}()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  ${action};
  RETURN NEW;
END;
$$
`)
    // CREATE OR REPLACE TRIGGER was only added on postgresql 14 so let's be a bit more portable for now:
    // https://stackoverflow.com/questions/35927365/create-or-replace-trigger-postgres
    await sequelize.query(`DROP TRIGGER IF EXISTS ${triggerName} ON "${model.tableName}"`)
    await sequelize.query(`CREATE TRIGGER ${triggerName}
  ${after} ${on.toUpperCase()}
  ON "${model.tableName}"
  FOR EACH ROW${when}
  EXECUTE PROCEDURE ${functionName}();
`)
  } else if (sequelize.options.dialect === 'sqlite') {
    await sequelize.query(`
CREATE TRIGGER IF NOT EXISTS ${triggerName}
  ${after} ${on.toUpperCase()}
  ON "${model.tableName}"
  FOR EACH ROW${when}
  BEGIN
    ${action};
  END;
`)
  }
}

const Post = sequelize.define('Post', {
  title: { type: DataTypes.STRING },
});
const User = sequelize.define('User', {
  username: { type: DataTypes.STRING },
  postCount: { type: DataTypes.INTEGER },
});
User.hasMany(Post)
Post.belongsTo(User)
await sequelize.sync({ force })
await createTrigger(sequelize, Post, 'insert', `UPDATE "${User.tableName}" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId"`)
await createTrigger(sequelize, Post, 'delete', `UPDATE "${User.tableName}" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId"`)
await createTrigger(
  sequelize,
  Post,
  'update',
  `UPDATE "${User.tableName}" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
UPDATE "${User.tableName}" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId"`,
  {
    when: 'OLD."UserId" <> NEW."UserId"',
  }
)

async function reset() {
  const user0 = await User.create({ username: 'user0', postCount: 0 });
  const user1 = await User.create({ username: 'user1', postCount: 0 });
  await Post.create({ title: 'user0 post0', UserId: user0.id });
  await Post.create({ title: 'user0 post1', UserId: user0.id });
  await Post.create({ title: 'user1 post0', UserId: user1.id });
  return [user0, user1]
}
let rows, user0, user1
[user0, user1] = await reset()

// Check that the posts created increased postCount for users.
rows = await User.findAll({ order: [['username', 'ASC']] })
common.assertEqual(rows, [
  { username: 'user0', postCount: 2 },
  { username: 'user1', postCount: 1 },
])

// UPDATE the author of a post and check counts again.
await Post.update({ UserId: user1.id }, { where: { title: 'user0 post1' } })
rows = await User.findAll({ order: [['username', 'ASC']] })
common.assertEqual(rows, [
  { username: 'user0', postCount: 1 },
  { username: 'user1', postCount: 2 },
])

// DELETE some posts.

await Post.destroy({ where: { title: 'user0 post1' } })
rows = await User.findAll({ order: [['username', 'ASC']] })
common.assertEqual(rows, [
  { username: 'user0', postCount: 1 },
  { username: 'user1', postCount: 1 },
])

await Post.destroy({ where: { title: 'user0 post0' } })
rows = await User.findAll({ order: [['username', 'ASC']] })
common.assertEqual(rows, [
  { username: 'user0', postCount: 0 },
  { username: 'user1', postCount: 1 },
])

})().finally(() => { return sequelize.close() })

pacakge.json

{
  "name": "tmp",
  "private": true,
  "version": "1.0.0",
  "dependencies": {
    "pg": "8.5.1",
    "pg-hstore": "2.3.3",
    "sequelize": "6.14.0",
    "sql-formatter": "4.0.2",
    "sqlite3": "5.0.2"
  }
}

生成的触发器定义查询示例：
SQLite：

CREATE TRIGGER IF NOT EXISTS Posts_insert
  AFTER INSERT
  ON "Posts"
  FOR EACH ROW
  BEGIN
    UPDATE "Users" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId";
  END;
CREATE TRIGGER IF NOT EXISTS Posts_delete
  AFTER DELETE
  ON "Posts"
  FOR EACH ROW
  BEGIN
    UPDATE "Users" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
  END;
CREATE TRIGGER IF NOT EXISTS Posts_update
  AFTER UPDATE
  ON "Posts"
  FOR EACH ROW
  WHEN (OLD."UserId" <> NEW."UserId")
  BEGIN
    UPDATE "Users" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
UPDATE "Users" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId";
  END;

PostgreSQL：

CREATE OR REPLACE FUNCTION Posts_insert_fn()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  UPDATE "Users" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId";
  RETURN NEW;
END;
$$
DROP TRIGGER IF EXISTS Posts_insert ON "Posts"
CREATE TRIGGER Posts_insert
  AFTER INSERT
  ON "Posts"
  FOR EACH ROW
  EXECUTE PROCEDURE Posts_insert_fn();
CREATE OR REPLACE FUNCTION Posts_delete_fn()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  UPDATE "Users" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
  RETURN NEW;
END;
$$
DROP TRIGGER IF EXISTS Posts_delete ON "Posts"
CREATE TRIGGER Posts_delete
  AFTER DELETE
  ON "Posts"
  FOR EACH ROW
  EXECUTE PROCEDURE Posts_delete_fn();
CREATE OR REPLACE FUNCTION Posts_update_fn()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  UPDATE "Users" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
UPDATE "Users" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId";
  RETURN NEW;
END;
$$
DROP TRIGGER IF EXISTS Posts_update ON "Posts"
CREATE TRIGGER Posts_update
  AFTER UPDATE
  ON "Posts"
  FOR EACH ROW
  WHEN (OLD."UserId" <> NEW."UserId")
  EXECUTE PROCEDURE Posts_update_fn();

总有一天我会把这个合并回来的。总有一天。
在Ubuntu 22.10，PostgreSQL 13.6上测试。

赞(0）回复(0）举报 2023-05-16

vom3gejh3#

您可以像这样在Sequelize中添加触发器。[正如@Evan Siroky提到的，我只是添加了几个步骤以获得更多细节]

import { Sequelize } from "sequelize";

const sequelize = new Sequelize({  host: "localhost",
                                   port: 3306,
                                   dialect: "MySQL",
                                   username: "your user name",
                                   password: "your password",
                                   database: "dbname",
                                   logging: false,});

sequelize.query('CREATE TRIGGER create_config AFTER INSERT ON users' +
  ' FOR EACH ROW' +
  ' BEGIN' +
  ' insert into configs (UserId) values(new.id);' +
  'END;')

赞(0）回复(0）举报 2023-05-16

我来回答

mysql 如何在SEQUELIZE(nodeJS)中创建触发器？

3条答案

相关问题

热门标签

最新问答