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

1 comment:

Alwin Co Daan said...

This is an amazing blog,it gives very helpful messages to us.Besides that Wisen has established as Best Dot Net Training in Chennai. or learn thru ASP.NET Online Training . Nowadays Dot Net has tons of job opportunities on various vertical industry.