Search This Blog

Setup Default value from another table

 

 By default, SQL server won't allows you to set default value from another table, and I got few queries on how to perform this. You can do this with the help of Triggers.  Although I won't suggest the use of triggers except for auditing purpose, there are few situations where we can't eliminate their usage.

 

 
CREATE TABLE LookUpTable (id INT IDENTITY PRIMARY KEY , Value VARCHAR(100) )
INSERT INTO LookUpTable SELECT 'One' UNION ALL SELECT 'Two' UNION ALL SELECT 'Three' UNION ALL SELECT 'Four'
GO
CREATE TABLE ChildTable (id INT, Value VARCHAR(100))
GO

ALTER TRIGGER t_i_ModifyDefault
ON ChildTable
FOR INSERT

AS
BEGIN
IF EXISTS (SELECT 1 FROM inserted WHERE Value IS NULL)
BEGIN
UPDATE c
SET Value = l.Value
FROM ChildTable c
INNER JOIN LookUpTable l ON l.id = c.id
INNER JOIN inserted i ON i.id = c.id WHERE i.Value IS null
END

END

GO

INSERT INTO ChildTable(id) SELECT 1
INSERT INTO ChildTable SELECT 8, 'two'
SELECT * FROM ChildTable

No comments: