I am trying to change the value of defaults column from Y to N if a new value is inserted. The default column is now only restricted to have only 1 Y based on the different membershipType.
ID titlePromo membershipType defaults -- ---------- -------------- ------ 1 Promo 1 Membership Promotion Y 2 Promo 2 Membership Renewal Y 3 Promo 3 Membership Grad Y 4 Promo 4 Membership Promotion N 5 Promo 5 Membership Promotion N 6 Promo 6 Membership Grad N
Promo 1 defaults column value = 'Y'
However, a new 'Y' defaults value is inserted in either existing row with the same membership Type or a newly created row with the same membership Type. So once a default value is being changed to 'Y', the defaults column value in promo 1 will automatically changed to 'N'.
How do i achieve that?
CREATE TRIGGER trgDefaults ON FYPDB.dbo.Promotions AFTER INSERT, UPDATE AS BEGIN; UPDATE FYPDB.dbo.Promotions SET defaults = 'N' WHERE defaults = 'Y' AND membershipType IN (SELECT membershipType FROM Inserted); END
The trigger control that i used:
USE FYPDB GO CREATE TRIGGER trgDefaults ON dbo.Promotions AFTER INSERT, UPDATE AS BEGIN; UPDATE dbo.Promotions SET defaults = 'N' WHERE defaults = 'Y' AND membershipType IN (SELECT membershipType FROM Inserted); END
However, it changes my insert defaults value from a 'Y' to a 'N'. It's not what i wanted.