Default value binding of columns using Trigger

Question

Post reply

Default value binding of columns using Trigger

See-Quel

SSC Veteran

Points: 286
More actions
November 30, 2009 at 7:23 am

#206789

Hi,
I want to default a value for one of the columns. When inserting a record, if the value of 2nd column is NULL, it should take the value of 1st column. To describe the scenario, consider -
1) table with 2 columns - FirstName, LastName
2) LastName should take the value of FirstName, if we did not pass any value for LastName
(INSERT INTO tblperson (FirstName) VALUES ('A'))
After inserting, Column LastName should also have value = 'A'
3) I have a trigger which does that, but the problem is even if I pass the explicit value of LastName = 'B', it overwrites it to 'A'. Here is the trigger -
CREATE TRIGGER tgrDefaultValue ON tblPerson
FOR INSERT
AS
UPDATE tblPerson
SET LastName = (SELECT FirstName FROM tblPerson)
INSERT INTO tblperson (FirstName) VALUES ('A')
To solve this problem, I modified it to -
CREATE TRIGGER tgrDefaultValue ON tblPerson
FOR INSERT
AS
IF (SELECT LastName FROM tblPerson) = NULL
BEGIN
UPDATE tblPerson
SET LastName = (SELECT FirstName FROM tblPerson)
END
INSERT INTO tblperson (FirstName) VALUES ('A')
This adds a record to the table with NULL value for LastName. I even tried using "INSTEAD OF" but it didn't help me.
Can anyone suggest me what is missing here, or the best solution / strategy to capture this scenario?
Thanks in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

See-Quel SSC Veteran Points: 286 More actions · Answer 1

I think I figured out where the problem was, the IF syntax apparently wasn't correct. The parentheses were missing.

Here is the modified working version -

CREATE TRIGGER tgrDefaultValue ON tblPerson

FOR INSERT

AS

IF ((SELECT LastName FROM tblPerson) IS NULL)

BEGIN

UPDATE tblPerson

SET LastName = (SELECT FirstName FROM tblPerson)

END

Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 2

i think this is more like what you want:

CREATE TRIGGER tgrDefaultValue ON tblPerson

FOR INSERT

AS

UPDATE tblPerson

SET LastName = FirstName

FROM INSERTED

WHERE tblPerson.ID = INSERTED.ID

AND tblPerson.LastName IS NULL

AND INSERTED.FirstName IS NOT NULL

END

your old trigger had some logic holes in it: first, a trigger should always refer to the virtual tables INSERTED and DELETED; yours would have updated the entire table every time, instead of just what was inserted.

your select to get the last name (LastName = (SELECT FirstName FROM tblPerson))

would have been incorrect if more than one record was inserted...it would only get one name, but update it for all rows.

a critical piece is the INSERTED and real table have to be joined...i assumed a column name dID, but you would know the real column name.

hope this gets you pointed int he right direction.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

See-Quel SSC Veteran Points: 286 More actions · Answer 3

Thanks Lowell. I haven't worked with triggers before. I appreciate for letting me know how a trigger should be designed and work. I tested your code, it needed a little modification -

CREATE TRIGGER tgrDefaultValue ON tblPerson

FOR INSERT

AS

BEGIN

UPDATE tblPerson

SET tblPerson.LastName = Inserted.FirstName

FROM Inserted

WHERE tblPerson.PersonID = Inserted.PersonID

AND tblPerson.LastName IS NULL

AND Inserted.FirstName IS NOT NULL

END

SET tblPerson.LastName = Inserted.FirstName

Is this statement correct logically? I tested it and works fine.

Thanks again 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 4

that's what i was hoping....that there was an Id, in your case, PersonId in the table. your modification looks good.

you can easily test it with this, which would insert 4 rows as a single update:

INSERT INTO tblperson (FirstName)

SELECT 'One fish' UNION ALL

SELECT 'Two fish' UNION ALL

SELECT 'Red fish' UNION ALL

SELECT 'Blue fish'

at the end, all four "new" rows should have the last name populated due to the trigger.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

See-Quel SSC Veteran Points: 286 More actions · Answer 5

Yeah, I tested it before and it worked pretty fine. So, now I know a little bit of simple Triggers. This works well when bulk inserting records from a csv file -

BULK INSERT tblPerson

FROM 'C:\Person.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '(newline symbol)',

FIRE_TRIGGERS

)

Thanks a lot for guiding and helping me out 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 6

glad i could help!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!