0

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!

Netram
  • 21
  • 6

1 Answers1

2

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

Daniel
  • 1,392
  • 1
  • 5
  • 16
  • This was very helpful! thank you! but for an odd reason I eventually had to change the 1 in to a 0+1 because it would assign the role 0 to every newly created account. Reason still unknown. – Netram Mar 28 '22 at 10:53
  • @marten.philip what type are you using for roleID? (e.g. INT) – Daniel Mar 30 '22 at 23:55