SQL server auto update column

Question

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.

Answer 1

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));

• lel: Let us continue this discussion in chat.