CODEWITHSUNDEEP
mysqlsequelize.jsdatabase-triggeramazon-aurora
Jozef
Jozef

Reputation: 493

Sequelize - how to define a trigger in a model?

I am currently working with sequelize on RDS Aurora DB and I need to track which records in which tables were deleted - for this, I created a new table dbLog. Now I need to add a trigger to the database which saves the id of the record into dbLog table whenever that record in table t1 gets deleted. Basically, I need to cover two scenarios for already deployed databases and those who dont yet exist.

  1. Database already exists. This is easy since I can create the trigger by raw query like this
CREATE TRIGGER trigger AFTER DELETE ON t1 FOR EACH ROW 
INSERT INTO dbLog ( id, tableName, status, updatedAt ) 
VALUES (OLD.id, 't1', 'D', NOW())`
  1. Database doesn't exist. This is problematic since it gets created by initiation of the model and then sequelize.sync(). So I cant just call a raw query, instead, I need to define this trigger in the model for t1 table. This is how I initiate the table (simplified)
t1.init(
        {
            id: {
                type: new DataTypes.BIGINT,
                autoIncrement: true,
                primaryKey: true
            },
            name: {
                type: new DataTypes.STRING,
                allowNull: false
            }    
        },
        {
            sequelize,           
            tableName: 't1',
        }
    );

The problem is that I dont know how to create that trigger. I tried putting something like this into the attributes section of t1.Init. But there is some problem, when I check the database for triggers, none was created. How can I fix it? And are even triggers created by this way 1:1 equivalent of triggers created by raw query? Thanks a lot.

hooks: {
    afterDestroy: function(t1) {
        DbLog.create({
           id: t1.id,
           tableName: 't1',
           status: 'D',
           updatedAt: '2015-10-02'
           })
       }
   }

Upvotes: 2

Views: 1410

Answers (1)

Anatoly
Anatoly

Reputation: 22803

You should create triggers in a DB manually by executing a raw SQL query. If you are using migrations then just create a migration with a trigger creation (also a raw SQL query).

Upvotes: 2

Related Questions