Query sequence - SQL Server Forums

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

Query sequence

Author

Topic

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-26 : 06:11:41

Hi guys, hope everyone is well.

I wonder how you would set up a query that would be activated when a new line in table1 is inserted, it uses those new details in table1 to run a query on table2 and insert the results into table3. Is that possible? What is the best way of doing so?

Thanks everyone

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-26 : 06:31:20

More info to further explain. For example:
In table1 a new line is inserted under the columns "gender" and "haircolour", with the data Male and Brown. The query then searches table2 for all the entries in the table with Male and Brown under their respective columns. The query only needs to return details of the three oldest Males with Brown hair. The three results are then inserted into table3.

The query criteria from table1 will be different every time, for example Female Blond, but the query always needs to return the three oldest from table2 (Table2 will obviously include columns "Gender" "Haircolour" and "Age")and insert the results into tabl3

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 07:06:11

what you need is a insert trigger on table1. Each time insert happens, the trigger will fire and you can write your logic inside the trigger code which will be executed each time.it will be something like

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.datefield ASC
END

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-26 : 07:11:25

quote:
Originally posted by visakh16

what you need is a insert trigger on table1. Each time insert happens, the trigger will fire and you can write your logic inside the trigger code which will be executed each time.it will be something like

ORDER BY t2.datefield ASC
END[/code]

What is t2.datefield? do you mean "age"? I'm new at this, Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 07:16:03

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

what you need is a insert trigger on table1. Each time insert happens, the trigger will fire and you can write your logic inside the trigger code which will be executed each time.it will be something like

ORDER BY t2.datefield ASC
END[/code]

What is t2.datefield? do you mean "age"? I'm new at this, Thanks

On basis of what column value you need oldest 3 records? that column should be put in place of datefield. i thought you have an audit date column in your table like datecreated,modified,...

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-26 : 07:19:29

quote:
Originally posted by visakh16

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

what you need is a insert trigger on table1. Each time insert happens, the trigger will fire and you can write your logic inside the trigger code which will be executed each time.it will be something like

ORDER BY t2.datefield ASC
END[/code]

What is t2.datefield? do you mean "age"? I'm new at this, Thanks

On basis of what column value you need oldest 3 records? that column should be put in place of datefield. i thought you have an audit date column in your table like datecreated,modified,...

In table2 I have columns "Gender" "Haircolour" and "Age". So when the details of the correct gender and hair colour are found, the oldest three need to be inserted into table3.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 07:22:00

still you didnt answer my question. oldest based on what? you need to have a field based on which you want to receive oldest three.I think since you're searching by Gender & Haircolour the only possibility is age if you dont have any other columns in table2.

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-26 : 07:25:00

quote:
Originally posted by visakh16

still you didnt answer my question. oldest based on what? you need to have a field based on which you want to receive oldest three.I think since you're searching by Gender & Haircolour the only possibility is age if you dont have any other columns in table2.

Sorry, I miss understood.

I mean oldest as in age of the person with those details as in a Male with Brown hair who is 19years old (Age as in a persons age).

Sorry for the miss-understanding. Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 07:28:06

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

still you didnt answer my question. oldest based on what? you need to have a field based on which you want to receive oldest three.I think since you're searching by Gender & Haircolour the only possibility is age if you dont have any other columns in table2.

Sorry, I miss understood.

I mean oldest as in age of the person with those details as in a Male with Brown hair who is 19years old (Age as in a persons age).

Sorry for the miss-understanding. Thank you

then it should be like below

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

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-26 : 07:37:09

quote:
Originally posted by visakh16

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

still you didnt answer my question. oldest based on what? you need to have a field based on which you want to receive oldest three.I think since you're searching by Gender & Haircolour the only possibility is age if you dont have any other columns in table2.

Sorry, I miss understood.

I mean oldest as in age of the person with those details as in a Male with Brown hair who is 19years old (Age as in a persons age).

Sorry for the miss-understanding. Thank you

then it should be like below

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

Thank you very much. Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 07:42:45

You're welcome

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-29 : 12:19:48

quote:
Originally posted by fastmichaels

More info to further explain. For example:
In table1 a new line is inserted under the columns "gender" and "haircolour", with the data Male and Brown. The query then searches table2 for all the entries in the table with Male and Brown under their respective columns. The query only needs to return details of the three oldest Males with Brown hair. The three results are then inserted into table3.

The query criteria from table1 will be different every time, for example Female Blond, but the query always needs to return the three oldest from table2 (Table2 will obviously include columns "Gender" "Haircolour" and "Age")and insert the results into tabl3

Thanks in advance

The answer I recieved for this works brilliantly, it was:

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

But now I have realised I need two fields from the table1 inserted along side the results in table3,

For example a new line in table1 is inserted under four columns "gender" and "haircolour"(which is the search criteria) and "thirdcolumn" and "forthcolumn" (Non-search criteria). I want the trigger to insert the top three results into table3 (Which it currently does) but now I want it to also insert the details in "thirdcolumn" and "forthcolumn" from Table1 on the lines as well. So table3 will display three lines of results with the details of "thirdcolumn" and "forthcolumn" from table1 on each line(The same data on each line).

So how would I adapt the above trigger to display the third and forth column data from table1 in on the search result lines of table3.

Really appreciate your help everyone.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 12:22:15

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

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-29 : 13:07:41

quote:

Originally posted by visakh16

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

Thanks again, you are a star!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 13:10:27

welcome

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-29 : 13:59:42

quote:

Originally posted by fastmichaels

quote:

Originally posted by visakh16

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

Thanks again, you are a star!

Sorry, it is me again,

I have just tried this script, but now my table1 is not being allowed to be updated, an error message reads "Error converting data type varchar to numeric", if I delete the trigger altogether it is fine, if I set the trigger to how it was before its fine, but with the new addition, table1 is being prevented from updating i.e "INSERT INTO table1 (abcd,efgh,......) values (1234,5678,.....) "Error converting data type varchar to numeric"

I wonder if it is anything to do with where you have put:

SELECT TOP 3 t2.field1,t2.field2,...[blue],i.thirdcolumn,i.fourthcolumn
FROM table2 t2

Do I need to put in a refertence to the table1 after "From table2 t2"

Any ideas?

Thank you

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-29 : 14:56:12

Any ideas?

Thanks everyone, really appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 03:40:39

are you sure the new columns and target coluns in table 3 are of same type?

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-30 : 07:05:41

quote:
Originally posted by visakh16

are you sure the new columns and target coluns in table 3 are of same type?

I have just cracked it, the columns were in the wrong order, the new fields to go in table3 were at the biginning of the table, but I was specifying them at the end in the trigger.

So it works now, thank you
Best regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 07:25:20

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources