CODEWITHSUNDEEP
mysqltriggers
deepak.mr888
deepak.mr888

Reputation: 349

How to create a before insert trigger based on previously inserted values in the table

I have a table which contains two columns 1.Clanid 2.Active

My problem is that i dont want any value in Clanid column to be inserted in this table if this value is already in Clanid column and Active for that value is 1.

For example
Clanid Active
1         1
2         1
3         0

Now it should not be possible to insert a record with Clanid=1 and Active=1 but i can insert Clanid=3 and Active=1 as this record is not there.

Upvotes: 1

Views: 48

Answers (2)

Bohemian
Bohemian

Reputation: 424973

Try this:

delimiter //
create trigger unique_clanid
before insert on mytable
for each row
begin
  if new.active and exists (
    select * from mytable
    where clanid = new.clanid
    and active) then
    signal sqlstate '02000' set MESSAGE_TEXT = 'Duplicate ClanID';
  end if;
end//
delimiter ;

Upvotes: 1

NuongNguyen
NuongNguyen

Reputation: 126

I think you should handle in your app level, but you want to handle in DB lavel, you can write trigger check it you can check count record ( where Clanid = @param and Active =1) if count > 1 : rollback

I am not available mysql to test , i just can describe my solution as following ( i;m not sure the syntax correct, it is too long time i don't write trigger in mysql) CREATE TRIGGER test BEFORE INSERT ON table_name FOR EACH ROW BEGIN DECLARE newClanId integer; DECLARE counts integer; Set @newClanId = NEW.Clanid; Set @count := (SELECT count (*) FROM table_name WHERE Clanid = @newClanId and Active =1) IF @count > 1 THEN ROLLBACK; END;

Upvotes: 0

Related Questions