CODEWITHSUNDEEP
mysqlsqltriggers
Netram
Netram

Reputation: 21

Using a trigger to automatically assign a value to specific column on a new insert

I'm struggling to find proper information on SQL Triggers.

My previous question got removed for it not being specific enough so hopefully this will do better.

I am trying to create a trigger that will assign RoleID 1 to every newly inserted row to my users table.

But I can't seem to figure it out.

AFTER INSERT on users
on EACH ROW
insert into users.RoleID values(1);

This doesn't seem to work.

And the examples and or information regarding triggers all focus on alerts or sending emails after an insert/drop or update.

Can it actually be done?

Any help would be much appreciated.

Cheers!

Upvotes: 0

Views: 1497

Answers (1)

Daniel
Daniel

Reputation: 1472

It looks like you aren't creating you sql trigger with the correct keyword.

Try this

drop trigger if exists before_insert_users;
DELIMITER $$
CREATE TRIGGER before_insert_users
    BEFORE INSERT ON users
    FOR EACH ROW
       BEGIN
            SET NEW.RoleID = 1;
        END$$
DELIMITER ;

Note: RoleID will need to actually be a column on your table for this to work.

However, a trigger is not the best way to do this... See this SO post : Add a column with a default value to an existing table in SQL Server

Upvotes: 2

Related Questions