SQL server auto update column 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.

The Table: 

 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


Example:
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.
You need to update the defaults column by using an insert trigger on this table. The only problem with it would be that your trigger will fail on multirow-insert.
There is a correction in the WHERE clause you are using. Following is the code you should use:

UPDATE FYPDB.dbo.Promotions 
SET defaults = 'N' 
WHERE ID NOT IN (SELECT ID FROM Inserted) AND membershipType IN (SELECT membershipType FROM Inserted) AND EXISTS (SELECT * FROM Inserted WHERE defaults = 'Y'); 
UPDATE FYPDB.dbo.Promotions 
SET defaults = 'Y' 
WHERE ID IN (SELECT ID FROM Inserted) AND NOT EXISTS (SELECT * FROM FYPDB.dbo.Promotions p WHERE p.defaults = 'Y' and p.membershipType in (SELECT membershipType FROM Inserted));