Creating triggers with SQLite

How is everyone handling trigger statements at the moment? I've tried adding something like:

CREATE TRIGGER `new_user` AFTER INSERT ON `users` BEGIN
INSERT INTO `user_org` (`user_id`, `org_id`) VALUES (NEW.id, last_insert_rowid ());
END;

CREATE TRIGGER `new_user` AFTER INSERT ON `users` BEGIN
INSERT INTO `user_org` (`user_id`, `org_id`) VALUES (NEW.id, last_insert_rowid ());
END;

to the generated migration file, but it never ended up working properly. Are we supposed to just make transaction or batch functions instead?

5 Replies

Angelelz•12mo ago

Manually adding your trigger to a migration file before it's applied is the way to go unfortunately

m&traOP•12mo ago

I've tried doing this, but it doesn't seem to work--maybe I'm doing something wrong. This is what I'm adding to the end of my migration file:

--> statement-breakpoint
CREATE TRIGGER IF NOT EXISTS `new_user_org` AFTER INSERT ON `users`
BEGIN
    INSERT INTO `orgs` (`name`) VALUES (NEW.email);
    INSERT INTO `user_org` (`user_id`, `org_id`) VALUES (NEW.id, last_insert_rowid ());
END;

--> statement-breakpoint
CREATE TRIGGER IF NOT EXISTS `new_user_org` AFTER INSERT ON `users`
BEGIN
    INSERT INTO `orgs` (`name`) VALUES (NEW.email);
    INSERT INTO `user_org` (`user_id`, `org_id`) VALUES (NEW.id, last_insert_rowid ());
END;

After adding this, I just npm exec drizzle-kit push:sqlite to apply?

Angelelz•12mo ago

Nope, push will not take your migration files Into account If you want to apply migrations with drizzle, you'll need to create a migration script

Angelelz•12mo ago

https://orm.drizzle.team/kit-docs/overview#running-migrations

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

m&traOP•12mo ago

It works now... my migration script was pointing to the wrong DB this whole time pepeQQ

@Angelelz ! FWIW, I did make a working trigger function using a transaction:

export async function addUserOrg(email: string) {
    //@ts-expect-error trigger workaround
    await useDB().transaction(async tx => {
        await tx.insert(users).values({ email: email }).run();
        const [newUser] = await tx.select().from(users).where(eq(users.email, email));

        await tx.insert(orgs).values({ name: email }).run();
        const [newOrg] = await tx.select().from(orgs).where(eq(orgs.name, email));

        await tx.insert(user_org).values({ user_id: newUser.id, org_id: newOrg.id, role: 'ADMIN'}).run();

        return newUser;
    }, { behavior: 'immediate' });
}

export async function addUserOrg(email: string) {
    //@ts-expect-error trigger workaround
    await useDB().transaction(async tx => {
        await tx.insert(users).values({ email: email }).run();
        const [newUser] = await tx.select().from(users).where(eq(users.email, email));

        await tx.insert(orgs).values({ name: email }).run();
        const [newOrg] = await tx.select().from(orgs).where(eq(orgs.name, email));

        await tx.insert(user_org).values({ user_id: newUser.id, org_id: newOrg.id, role: 'ADMIN'}).run();

        return newUser;
    }, { behavior: 'immediate' });
}

Gaming

Programming

Creating triggers with SQLite