April 28, 2010 at 8:13 am
I have a scenario where defaults for a certain table can be stored in a second table.
When creating a new row in the first table, I need to look up the value in the second table. If the value is stored, I use that value. If the value in the second table is NULL, I should insert the column default.
Here is a sample problem. The real data is proprietary.
I have a students table with a lunch period column. The lunch period can be set on the class table, in which case all students in that class would get that lunch period. If the lunch period is NULL on the class table, the column default of the student table should be used.
Is there a way to do this?
DECLARE @Class TABLE
(
ClassIDINT IDENTITY(1,1)
,NameVARCHAR(10)NOT NULL
,LunchPeriodINTNULL
)
DECLARE @Student TABLE
(
StudentIDINT IDENTITY(1,1)
,FirstNameVARCHAR(50)NOT NULL
,LastNameVARCHAR(50)NOT NULL
,LunchPeriodINTDEFAULT(4)NOT NULL
,ClassIDINTNOT NULL
)
-- insert some class data
INSERT INTO @Class (Name, LunchPeriod)
SELECT '10-A', 4
UNION ALL SELECT '10-B', NULL
UNION ALL SELECT '9-A', 3
UNION ALL SELECT '9-B', 4
UNION ALL SELECT '11-A', 4
UNION ALL SELECT '11-B', 5
-- lunch period should be the class lunch period
-- if null, use the column default on student table
-- now insert a new student for class 2
-- expecting 4 as the value for LunchPeriod
DECLARE @ClassID INT = 2
DECLARE @LunchPeriod INT
SELECT @LunchPeriod = LunchPeriod
FROM @Class
WHERE ClassID = @ClassID
-- this does not work:
INSERT INTO @Student(FirstName, LastName, ClassID, LunchPeriod)
SELECT 'John', 'Smith', @ClassID, @LunchPeriod
April 28, 2010 at 9:19 am
In order for the column default to be used you would have to use an insert statement that omitted that column.
Since you know the default (is 4) you could do this:
INSERT INTO @Student(FirstName, LastName, ClassID, LunchPeriod)
SELECT 'John', 'Smith', @ClassID, isnull(@LunchPeriod,4)
The probability of survival is inversely proportional to the angle of arrival.
April 28, 2010 at 9:22 am
I'm looking for a way to do it without hard-coding the column defaults.
This way, if the column default changes, the new records would be correct.
April 28, 2010 at 9:36 am
Then there are two other options:
1) check for null and use a 3 value insert statement if it is null, else insert 4 values.
2) select the column default value into a variable and use the variable in the isnull() instead of the hard coded value.
The probability of survival is inversely proportional to the angle of arrival.
April 28, 2010 at 9:37 am
how about selecting from the @Class table?
INSERT INTO @Student(FirstName, LastName, ClassID, LunchPeriod)
SELECT 'John', 'Smith', @ClassID, isnull(@LunchPeriod,LunchPeriod) from @Class where ClassId = @ClassID
Lowell
April 28, 2010 at 11:17 am
Lowell (4/28/2010)
how about selecting from the @Class table?
INSERT INTO @Student(FirstName, LastName, ClassID, LunchPeriod)
SELECT 'John', 'Smith', @ClassID, isnull(@LunchPeriod,LunchPeriod) from @Class where ClassId = @ClassID
I can't get this to work? When I run it I get this error:
Cannot insert the value NULL into column 'LunchPeriod', table '@Student'; column does not allow nulls. INSERT fails.
April 28, 2010 at 11:21 am
i think i had to change my data; you had one class with a null lunch:
SELECT '10-B', NULL
which i changed so it would work; i was kind of assuming the lunch was defined by the class unless otherwise overridden?
Lowell
April 28, 2010 at 11:22 am
sturner (4/28/2010)
Then there are two other options:1) check for null and use a 3 value insert statement if it is null, else insert 4 values.
2) select the column default value into a variable and use the variable in the isnull() instead of the hard coded value.
I also thought of a third way. Like this:
INSERT INTO @Student(FirstName, LastName, ClassID)
SELECT 'John', 'Smith', @ClassID
UPDATE @Student
SET LunchPeriod = @LunchPeriod
WHERE @LunchPeriod IS NOT NULL
I was hoping there was some built-in way to do this, but I guess not 🙁
Thanks for your help!
April 28, 2010 at 11:23 am
Lowell (4/28/2010)
i think i had to change my data; you had one class with a null lunch:SELECT '10-B', NULL
which i changed so it would work; i was kind of assuming the lunch was defined by the class unless otherwise overridden?
The idea is that the class lunch can be NULL. In that case we use the column defaults.
April 28, 2010 at 11:36 am
but your @Student table does not allow nulls for the lunch; that is the constraint failure;:
,LunchPeriod INT DEFAULT(4) NOT NULL
that is the part that faisl...if you change that to allow a null lunch, i think you'd get what you want
Lowell
April 28, 2010 at 11:45 am
Lowell (4/28/2010)
but your @Student table does not allow nulls for the lunch; that is the constraint failure;:,LunchPeriod INT DEFAULT(4) NOT NULL
that is the part that faisl...if you change that to allow a null lunch, i think you'd get what you want
If I remove the constraint, I will get a NULL LunchPeriod.
I can not do that because LunchPeriod is required.
My requirement is that if Class.LunchPeriod is NULL, I should use Student.LunchPeriod column default.
April 28, 2010 at 1:58 pm
Students that need to loose weight should have a NULL lunch period.
The probability of survival is inversely proportional to the angle of arrival.
April 28, 2010 at 3:59 pm
sturner (4/28/2010)
Students that need to loose weight should have a NULL lunch period.
:hehe::hehe:
April 29, 2010 at 4:11 am
USE tempdb;
GO
CREATE TABLE dbo.Class
(
ClassID INTEGER IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(10) NOT NULL,
LunchPeriod INTEGER NULL
);
CREATE TABLE dbo.Student
(
StudentID INTEGER IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
LunchPeriod INTEGER NOT NULL DEFAULT (4),
ClassID INTEGER NOT NULL REFERENCES dbo.Class
);
INSERT dbo.Class
(
Name,
LunchPeriod
)
VALUES ('10-A', 4),
('10-B', NULL),
('9-A', 3),
('9-B', 4),
('11-A', 4),
('11-B', 5);
GO
CREATE TRIGGER [trg dbo.Student IOI]
ON dbo.Student
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM inserted) RETURN;
INSERT dbo.Student
(
FirstName,
LastName,
ClassID,
LunchPeriod
)
SELECT INS.FirstName,
INS.LastName,
INS.ClassID,
COALESCE(INS.LunchPeriod, C.LunchPeriod)
FROM inserted INS
JOIN dbo.Class C
ON C.ClassID = INS.ClassID
WHERE ISNULL(INS.LunchPeriod, C.LunchPeriod) IS NOT NULL;
INSERT dbo.Student
(
FirstName,
LastName,
ClassID
)
SELECT INS.FirstName,
INS.LastName,
INS.ClassID
FROM inserted INS
JOIN dbo.Class C
ON C.ClassID = INS.ClassID
WHERE ISNULL(INS.LunchPeriod, C.LunchPeriod) IS NULL;
END;
GO
INSERT dbo.Student
(
FirstName,
LastName,
ClassID,
LunchPeriod
)
SELECT 'John',
'Smith',
2,
NULL;
GO
SELECT *
FROM dbo.Student
GO
DROP TABLE dbo.Student;
DROP TABLE dbo.Class;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 6:50 am
Thanks for your help.
This solution won't work for me, I have multiple columns with these requirements and that would cause the trigger to be very complex.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply