Need an example of a trigger.

Hello,

I'm a newbie to the MS SQL db engine, and I'd like to have some help with Transact-SQL. I'd like to know the code to create a trigger to do the following. I have two tables, one references the other. I'd like the database to automatically delete the records from table2 when the record that they reference in table1 is deleted. Much thanks.Try this

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin

declare @.refcolumn datatypehere
select @.refcolumn = deleted.refcolumn from deleted
delete from table2 where table2refcolumn = @.refcolumn

end|||how about:

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete from table2 where refcolumn in (select refcolumn from deleted)
end

or

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete t2
from table2 t2
join deleted d on t2.refcolumn = d.refcolumn
end

by using either of these you will delete all records when multipule records are deleted.

Nearest-neighbour logic - please help!

Hi,
Hope you can help me with this one. I'm building a search engine with
a SQL Server 2000 box in the backend. If a user gets no result for some
search but there is a close match - I want that match to be suggested
(or any others).
Now I believe that the technology to use is called: "Ternary Search
Trees" (due to a fast implementation of the Levenshtein Distance
algorithm). However in SQL Server 2000 the equivalent to sue is called
"indexed views".
My question is am I right? Can anyone out there give me a few pointers
i nthe right directions please? Any comments/suggestions/help greatly
appreciated...
Cheers,
Al.Hi,
Indexed views would have nothing in particular to do with advanced search
algorithms.
You may wish to investigate the SOUNDEX and associated functions. See Books
Online.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<almurph@.altavista.com> wrote in message
news:1163420393.594947.317930@.k70g2000cwa.googlegroups.com...
> Hi,
>
> Hope you can help me with this one. I'm building a search engine with
> a SQL Server 2000 box in the backend. If a user gets no result for some
> search but there is a close match - I want that match to be suggested
> (or any others).
> Now I believe that the technology to use is called: "Ternary Search
> Trees" (due to a fast implementation of the Levenshtein Distance
> algorithm). However in SQL Server 2000 the equivalent to sue is called
> "indexed views".
> My question is am I right? Can anyone out there give me a few pointers
> i nthe right directions please? Any comments/suggestions/help greatly
> appreciated...
> Cheers,
> Al.
>

Nearest-neighbour logic - please help!

Hi,
Hope you can help me with this one. I'm building a search engine with
a SQL Server 2000 box in the backend. If a user gets no result for some
search but there is a close match - I want that match to be suggested
(or any others).
Now I believe that the technology to use is called: "Ternary Search
Trees" (due to a fast implementation of the Levenshtein Distance
algorithm). However in SQL Server 2000 the equivalent to sue is called
"indexed views".
My question is am I right? Can anyone out there give me a few pointers
i nthe right directions please? Any comments/suggestions/help greatly
appreciated...
Cheers,
Al.Hi,
Indexed views would have nothing in particular to do with advanced search
algorithms.
You may wish to investigate the SOUNDEX and associated functions. See Books
Online.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<almurph@.altavista.com> wrote in message
news:1163420393.594947.317930@.k70g2000cwa.googlegroups.com...
> Hi,
>
> Hope you can help me with this one. I'm building a search engine with
> a SQL Server 2000 box in the backend. If a user gets no result for some
> search but there is a close match - I want that match to be suggested
> (or any others).
> Now I believe that the technology to use is called: "Ternary Search
> Trees" (due to a fast implementation of the Levenshtein Distance
> algorithm). However in SQL Server 2000 the equivalent to sue is called
> "indexed views".
> My question is am I right? Can anyone out there give me a few pointers
> i nthe right directions please? Any comments/suggestions/help greatly
> appreciated...
> Cheers,
> Al.
>

Nearest-neighbour logic - please help!

Hi,
Hope you can help me with this one. I'm building a search engine with
a SQL Server 2000 box in the backend. If a user gets no result for some
search but there is a close match - I want that match to be suggested
(or any others).
Now I believe that the technology to use is called: "Ternary Search
Trees" (due to a fast implementation of the Levenshtein Distance
algorithm). However in SQL Server 2000 the equivalent to sue is called
"indexed views".
My question is am I right? Can anyone out there give me a few pointers
i nthe right directions please? Any comments/suggestions/help greatly
appreciated...
Cheers,
Al.
Hi,
Indexed views would have nothing in particular to do with advanced search
algorithms.
You may wish to investigate the SOUNDEX and associated functions. See Books
Online.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<almurph@.altavista.com> wrote in message
news:1163420393.594947.317930@.k70g2000cwa.googlegr oups.com...
> Hi,
>
> Hope you can help me with this one. I'm building a search engine with
> a SQL Server 2000 box in the backend. If a user gets no result for some
> search but there is a close match - I want that match to be suggested
> (or any others).
> Now I believe that the technology to use is called: "Ternary Search
> Trees" (due to a fast implementation of the Levenshtein Distance
> algorithm). However in SQL Server 2000 the equivalent to sue is called
> "indexed views".
> My question is am I right? Can anyone out there give me a few pointers
> i nthe right directions please? Any comments/suggestions/help greatly
> appreciated...
> Cheers,
> Al.
>

NEAR syntax in Full-Text Search

In tuning our search engine which is running against SQL 2005, I'm trying to understand the "near" operator in my CONTAINSTABLE query. I'm doing a query like the following:

select * FROM CONTAINSTABLE(Catalog, *,
'FORMSOF(INFLECTIONAL,"class") OR
FORMSOF(INFLECTIONAL,"calendar") OR
("class" near "calendar")', 1000)

Entries that have class and calendar directly next to eachother are being ranked higher, as would be expected. But entries that have Class <word> calendar, are being ranked the exact same as they would be with just the two Inflectional ORs and no near syntax.

I thought the near syntax was supposed to be useful up to 50 words?

The near syntax is useful up to 50 words means if the two words are more than 50 words apart, the ranking score (of that OR clause) will be 0.

Ranking is rather complicated. In your example, the ranking will be affected by all 3 OR clauses. The ranking also depends on other factors, like document length, average document length of the corpus, frequency of the terms in the document and across corpus. So, word distance in the near clause may or may not be visible in your final ranking score.

NEAR syntax in Full-Text Search

In tuning our search engine which is running against SQL 2005, I'm trying to understand the "near" operator in my CONTAINSTABLE query. I'm doing a query like the following:

select * FROM CONTAINSTABLE(Catalog, *,
'FORMSOF(INFLECTIONAL,"class") OR
FORMSOF(INFLECTIONAL,"calendar") OR
("class" near "calendar")', 1000)

Entries that have class and calendar directly next to eachother are being ranked higher, as would be expected. But entries that have Class <word> calendar, are being ranked the exact same as they would be with just the two Inflectional ORs and no near syntax.

I thought the near syntax was supposed to be useful up to 50 words?

The near syntax is useful up to 50 words means if the two words are more than 50 words apart, the ranking score (of that OR clause) will be 0.

Ranking is rather complicated. In your example, the ranking will be affected by all 3 OR clauses. The ranking also depends on other factors, like document length, average document length of the corpus, frequency of the terms in the document and across corpus. So, word distance in the near clause may or may not be visible in your final ranking score.