SQL Server – How to Auto Update Column

sql server

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.

Best Answer

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