Returning a result no matter what

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning a result no matter what

Author

Topic

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 06:28:31

Hi, I am using this trigger and it works spiffing, but I want to adapt it so that if there are only 1 or 2 males with the matching hair colour that it selects them and then selects tha oldest male regardless of hair colour, just so that there is always a result of three. do you see understand?

the original code that visakh gave me is:

CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN
INSERT INTO table3
SELECT TOP 3 t2.field1,t2.field2,...
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
AND i.Haircolour=t2.Haircolour
ORDER BY t2.Age DESC
END

so for example if someone searches for a male with brown hair, the script would be asked to return a result of three different males with brown hair olddest first. But if there are only 2 males on the database with brown hair, it would select them and then the oldest male of any other hair colour. So there are allways three results in the set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 06:32:24

why do you want this logic to be implemented on trigger? do you want to do this everytime you perform some action on a table?

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 06:43:53

quote:
Originally posted by visakh16

why do you want this logic to be implemented on trigger? do you want to do this everytime you perform some action on a table?

I always want a result set of three, but there is no gurantee there will always be a results set of three matching the specific criteria. There may be none or one or two males with brown hair so I want to return as many males with brown hair up to a max of three oldest first, but if there is only two with brown hair i want them then a the oldest out of the remaining males, regardless of hair colour, just so there is three results.

Evry time table one is updated I want this trigger to be performed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 07:20:25

[code]CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN

INSERT INTO table3
SELECT TOP 3 t2.field1,t2.field2,...,
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq,t2.Age DESC
END[/code]

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 08:03:22

quote:

Originally posted by visakh16

CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN

INSERT INTO table3
SELECT TOP 3 t2.field1,t2.field2,...,
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq,t2.Age DESC
END

I get a message "Incorrect syntax near "Case""

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 08:41:53

Show your code please

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 08:47:54

quote:
Originally posted by visakh16

Show your code please

CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN
INSERT INTO table3
SELECT TOP 3 t2.firstname,t2.lastname,t2.gender,t2.haircolour,t2.age
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq,t2.Age DESC
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 08:52:24

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

Show your code please

CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN
INSERT INTO table3
SELECT TOP 3 t2.firstname,t2.lastname,t2.gender,t2.haircolour,t2.age,
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq,t2.Age DESC
END
GO

missed a comma. see above

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 09:01:08

quote:
Originally posted by visakh16

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

Show your code please

CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN
INSERT INTO table3
SELECT TOP 3 t2.firstname,t2.lastname,t2.gender,t2.haircolour,t2.age,
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq,t2.Age DESC
END
GO

missed a comma. see above

Thank you, but now I am getting "Insert Error: Column name or number of supplied values does not match table definition" But I am using the same tables and fields as in the original trigger which worked fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 09:30:22

[code]CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN
INSERT INTO table3
SELECT firstname,lastname,gender,haircolour,age
FROM
(
SELECT TOP 3 t2.firstname,t2.lastname,t2.gender,t2.haircolour,t2.age,
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq,t2.Age DESC
)t
END
GO[/code]

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 09:41:07

quote:

Originally posted by visakh16

CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN
INSERT INTO table3
SELECT firstname,lastname,gender,haircolour,age
FROM
(
SELECT TOP 3 t2.firstname,t2.lastname,t2.gender,t2.haircolour,t2.age,
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq,t2.Age DESC
)t
END
GO

Is the bit that states:
"FROM
(
SELECT "

Is it supposed to have an open bracket and no table reference? I haven't tesated it yet.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 09:43:40

its start of dervied table. i've closed it down and given it alias t

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 10:04:55

quote:
Originally posted by visakh16

its start of dervied table. i've closed it down and given it alias t

O.k. I get ya. Well it works so thank you so much once again, you've been fantastic.

Best regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 10:07:55

No problem. You're welcome

Feel free to post your doubts

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 10:11:31

quote:
Originally posted by visakh16

No problem. You're welcome
Feel free to post your doubts

Thanks for that.

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 10:27:51

quote:
Originally posted by visakh16

No problem. You're welcome
Feel free to post your doubts

I again, I have just identified an anomily. The trigger appears to be giving priority to age rather than hair colour, so even if there are three males with brown hair it lists the the oldest male regardless of hair colour and the two oldest of the brown haired males.

I only want the trigger to pick out the oldest other hair colour if there is not enough brown haired males to male up the result of three.

any ideas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 10:33:39

[code]
CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN
INSERT INTO table3
SELECT firstname,lastname,gender,haircolour,age
FROM
(
SELECT TOP 3 t2.firstname,t2.lastname,t2.gender,t2.haircolour,t2.age,
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq DESC,t2.Age DESC
)t
END
GO
[/code]

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-21 : 11:41:50

quote:

Originally posted by visakh16


CREATE TRIGGER InsertInfo ON table1
AFTER INSERT
AS
BEGIN
INSERT INTO table3
SELECT firstname,lastname,gender,haircolour,age
FROM
(
SELECT TOP 3 t2.firstname,t2.lastname,t2.gender,t2.haircolour,t2.age,
CASE WHEN i.Haircolour=t2.Haircolour THEN 1 ELSE 0 END AS Seq
FROM table2 t2
INNER JOIN INSERTED i
ON i.gender=t2.gender
ORDER BY Seq DESC,t2.Age DESC
)t
END
GO

Ye it looks to be working, thank you again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 11:52:50

no worries

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources