December 18, 2015 at 9:25 am
I have a table employees with some columns.
I want to set the default value of Commission column to 1000 for those records whose value in Job column is Manager. Or in other words , if the Job value is ' Manager' then set the default value of Commission to '1000'. How can i do this using sql query?
December 18, 2015 at 9:51 am
The only way I know to do this is with a trigger. When there's an update, check to see if there are NULL values and 1s.
CREATE TABLE MyTable2
(id INT IDENTITY(1,1)
, manager TINYINT
, commission MONEY)
;
GO
INSERT dbo.MyTable2
( manager )
VALUES
( 0 )
, (1)
GO
CREATE TRIGGER mytable2_tri_default ON MyTable2
FOR INSERT, UPDATE
AS
BEGIN
UPDATE
dbo.MyTable2
SET
commission = 1000
WHERE
commission IS NULL
AND manager = 1;
END;
GO
SELECT top 10
*
FROM dbo.MyTable2 AS mt
GO
INSERT dbo.MyTable2
( manager )
VALUES
( 0 )
, (1)
GO
SELECT top 10
*
FROM dbo.MyTable2 AS mt
GO
Make sure you have indexes in place to support this, so index the manager + commission column so performance doesn't get horrible as the table grows.
You can also use a computed column instead of a real column for the commission if that works, but I'm guessing not.
December 18, 2015 at 4:53 pm
hlsc1983 (12/18/2015)
I have a table employees with some columns.I want to set the default value of Commission column to 1000 for those records whose value in Job column is Manager. Or in other words , if the Job value is ' Manager' then set the default value of Commission to '1000'. How can i do this using sql query?
Wup... hold the phone. Are you saying that 1) you just want to do an update or 2) are you saying you actually want that value to automatically be populated in the table if someone inserts a new "Manager" without inserting a value for Commission?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply