0

I have a table for Seller. If seller type is 'organization', I want the MidName of Seller to be blank (NULL). Is it possible to create a trigger that does so after insert?

Column names in table:

  • SellerID
  • Type(fixed value - either Org or Ind)
  • FirstName
  • MidName
  • LastName
  • Address
  • PhoneNo

If Type is "Org" then MidName must be updated to blank (even if some value is entered during Insert)

  • Please provide table definition(s), the actual insert statement and expected results after insert. – Scott Hodgin Aug 22 '16 at 11:02
  • How is the data getting into the database? If it's a stored procedure, you can check the values coming in and change the MidName before inserting the record. – WEI_DBA Aug 22 '16 at 15:04
3

I agree with Andriy that a constraint should be used, but to answer your question if this is possible with a trigger, the answer is yes.

Here is the trigger:

CREATE TRIGGER i_Seller
ON Seller
AFTER INSERT
AS
IF EXISTS (SELECT NULL FROM inserted i WHERE Type = 'Org')
BEGIN;
    UPDATE s
    SET MidName = NULL
    FROM Seller s
    JOIN inserted i ON s.SellerID = i.SellerID
    WHERE s.Type = 'Org';
END;
2

Not exactly an answer to the question as asked, but rather than having a trigger reset the MidName value, I would probably introduce a constraint to prevent the column from having a value:

ALTER TABLE dbo.Seller
ADD CONSTRAINT CK_Seller_MidName
CHECK (MidName IS NULL OR Type = 'Ind');
                -- or: OR Type <> 'Org'
2

Try something like this

CREATE TRIGGER ForceMidNameToNullOnTypeOrganizationAfterInsert ON Seller AFTER INSERT AS 
set nocount on;

UPDATE A 
    SET a.Midname=null
FROM 
    Seller a JOIN
    inserted b on
        b.SellerId=a.SellerId AND
        b.Type='Org' 
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.