DB Engine :: Infinite Recursive Trigger

May 28, 2015

I have a after update trigger that calculates one of the columns based on other column values. It has to be a trigger and only fires an update statement against the table itself when the column has changed.  At a simple level it is doing something similar to the code below

IF UPDATE(Col1)
update MyTable SET Col2 = Col1 WHERE Col2 <> Col1

It works everywhere except on one site where the trigger causes itself to recurse until it reaches the 32 level error. It can be fixed by checking whether there and any records in the inserted table at the top. Like so.

IF EXISTS (SELECT * FROM inserted)
begin
IF UPDATE(Col1)
update MyTable SET Col2 = Col1 WHERE Col2 <> Col1
end

However, I would like to know whether there is some system setting other than "nested triggers" that I am missing that would cause the behaviour.

View 8 Replies


ADVERTISEMENT

Infinite Recursive?

Nov 24, 1999

Hey there, Hopefully someone has a sec to help me out. I have a pretty tough query here for ya.

I want to turn this:

Parent | Child
Fred | Mark
John | Cindy
Mark | John
John | Suzy

into this:

Ancestor | Level | Descendant
Fred | 1 | Mark
Fred | 2 | John
Fred | 3 | Cindy
Fred | 3 | Suzy
Mark | 1 | John
Mark | 2 | Cindy
Mark | 2 | Suzy
John | 1 | Cindy
John | 1 | Suzy

I want to do this in a single select statement. No loops or cursors. Please tell me you are as stumped as I am so that I don't feel so dumb.

Thanks,
Jason
SQL DBA/MCSE

View 1 Replies View Related

CLR-Based Trigger? Recursive Trigger? Common Table Expression?

Nov 14, 2006

Hey,

I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.

A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.

A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.

Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.

If you need the design or create script (table layout), please let me know.

Thanks.

View 4 Replies View Related

Help On Recursive Trigger

Mar 17, 2008

need help with recursive trigger on multiple insert
i continue from this code
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3010163&SiteID=1
tnx for the help




Code Snippet
CREATE TRIGGER TRIGGER [dbo].[CopyTotb_pivot_e]
ON [dbo].[tb_pivot_e]
FOR INSERT
AS
IF @@ROWCOUNT = 0
RETURN
INSERT INTO tb_pivot_big
SELECT
[Fname],
[new_unit],
[mhlka_id],
[mhlka],
1,
[fld1],
NULL,
NULL,
[fld2],
NULL,
NULL,
[fld3],
NULL,
NULL,
[fld4],
NULL,
NULL,
[fld5],
NULL,
NULL,
[fld6],
NULL,
NULL,
[fld7],
NULL,
NULL,
[fld8],
NULL,
NULL,
[fld9],
NULL,
NULL,
[fld10],
NULL,
NULL,
[fld11],
NULL,
NULL,
[fld12],
NULL,
NULL,
[fld13],
NULL,
NULL,
[fld14],
NULL,
NULL,
[fld15],
NULL,
NULL,
[fld16],
NULL,
NULL,
[fld17],
NULL,
NULL,
[fld18],
NULL,
NULL,
[fld19],
NULL,
NULL,
[fld20],
NULL,
NULL,
[fld21],
NULL,
NULL,
[fld22],
NULL,
NULL,
[fld23],
NULL,
NULL,
[fld24],
NULL,
NULL,
[fld25],
NULL,
NULL,
[fld26],
NULL,
NULL,
[fld27],
NULL,
NULL,
[fld28],
NULL,
NULL,
[fld29],
NULL,
NULL,
[fld30],
NULL,
NULL,
[fld31],
NULL,
NULL
FROM inserted
INSERT INTO tb_pivot_big

SELECT
[Fname],
[new_unit],
[mhlka_id],
[mhlka],
2,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM inserted

TNX

View 2 Replies View Related

UPDATE TRIGGER WITHOUT RECURSIVE FIRING

Apr 11, 2001

I have an update trigger I created that updates a field based on the user who last updated the record. Under 7 the only way it would work was to have recursive triggers firing turned on. Under 2000 might there be a btter solution. The code is below. Thanks

CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as
-- updates record with sql user and timestamp
--created 11-28-00 tim cronin
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
UPDATE PATIENT_MEDICATION_DISPERSAL_
set MODIFIED_BY = @muser,
MODIFIED_TS = getdate()
from deleted dt
WHERE --DT.MODIFIED_BY <> 'DBO' AND
PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID
end

View 3 Replies View Related

Recursive Trigger For Cascading Delete Doesnt Go More Than One Level

Oct 3, 2005

hello guysi am using a table that its secondary key connected to its primary key...and as sql server 2000 doesnt allow cascade delete fore such,i had to write a trigger myselfso i wrote the following triggerCREAT TRIGGER nameON tableFOR DeleteASBEGINIF @@ROWCOUNT >0Delete from table where table.parentID in (select sortID from deleted);ENDthen i went to the table and i tried to delete...and it gave me an error....that there are records that have there parentID= sortID of the table i am trieng to delete...so i deleted the relationship...and kept the triggerand now ...when i delete one...it deletes one level down....but not more....i mean when i delete sortID=4it deletes all the records that has parentID=4...and NOT more..whereas my aim was to have it recursive not to have records lost in my databasehope i explained good as much as i hope to find an answer soon...a clear one...and thanks in advanced...

View 3 Replies View Related

Transact SQL :: Types Don't Match Between Anchor And Recursive Part In Column ParentID Of Recursive Query

Aug 25, 2015

Msg 240, Level 16, State 1, Line 14

Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,

DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),

[code]....

View 2 Replies View Related

How To Convert Recursive Function Into Recursive Stored Procedure

Jul 23, 2005

I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod

View 4 Replies View Related

DB Engine :: Calling Exe From Trigger

May 12, 2015

I tried to call a exe from sql server through trigger with xp_cmdshell, But its not working as expected, shows preemptive_os_pipeops in process under the activity monitor and the process got hang.

View 18 Replies View Related

DB Engine :: Trigger When A New Database Gets Created

Oct 22, 2015

I am looking to create a server wide trigger which will fire whenever a database gets created, I would like to know if there are any gotchas or anything that I need to look out for/test specifically.

The idea is to automatically grant some permissions and take some other actions whenever a database gets created.

View 6 Replies View Related

DB Engine :: Trigger Is Firing Multiple Times

Apr 23, 2015

we have a table in database , which has multiple triggers defined by Vendor. I have created our own custom trigger on this table also , which fires last. Goal of this custom trigger is to send an email, when ever update happens on table. But somehow trigger is firing multiple times for same update, so it is sending multiple email for same update also

View 7 Replies View Related

DB Engine :: Need DDL Trigger Before Server Instance Shutdown

Nov 24, 2015

I have SQL Server 2014 Enterprise Edition with a number of in-memory tables sitting in my database.When server is restarted it takes many hours to recover my database if there was data in these in-memory tables before shutdown.As a result, I need to clean up in-memory tables every time before server instance shutdown. This is really annoying and requires extra prescriptive actions for support team. Can I have DDL server/database level trigger to catch shutdown event and clean my data before instance goes down?

View 3 Replies View Related

DB Engine :: Logon Failed For Login Due To Trigger Execution

Jun 24, 2015

I have a logon trigger on a SQL Server 2008 R2 Express Advanced production database to prevent remote logons. The trigger works fine. When I need to connect via my local machine, remotely, I connect via a VPN, can connect with SSMS and do whatever I need. However, if I use a linked server on my local machine (still connected via VPN), I receive the logon error

Msg 17892, Level 14, State 1, Line 1
Logon failed for login 'sa' due to trigger execution.

The trigger is below and it logs any failures, except for the linked server.

If I disable the trigger, the linked server connects ok.

CREATE TRIGGER [tr_MasterLogon]
   ON  ALL SERVER  WITH EXECUTE AS 'sa'
   FOR LOGON
AS
BEGIN
 DECLARE
  @ClientAddress varchar(48) = (SELECT client_net_address

[Code] ....

View 5 Replies View Related

DB Engine :: Does Logon Trigger Create Internal Temporary Objects

Jun 4, 2015

I create a logon trigger on a sql server 2008 r2 instance,the trigger is very simple,like this:

CREATE TRIGGER tg_login
ON ALL SERVER
FOR LOGON
AS
IF ORIGINAL_LOGIN() IN('sa') AND HOST_NAME()='TestHost1'

[Code] ...

I use master.dbo.LoginRecord  table to record the bad logon. When the trigger is working ,latches produced sometimes,wait type is PAGELATCH_UP  and wait resource is 2:1:3. At this time, a large number of logon failed ,error message is  "Logon failed for login 'login name' due to trigger execution."

I think  logon trigger create internal temporary objects maybe,it is right?

View 13 Replies View Related

What Exactly Is Infinite Clickthrough?

Nov 1, 2007

Another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2347749&SiteID=1) got me wondering: what exactly is this "infinite clickthrough" feature that SSRS Enterprise Edition has? Searching for "infinite clickthrough" yields little but frustration - nearly all of the hits are merely pages explaining that Enterprise Edition has it and the other editions don't, without ever describing what exactly it is.

Near as I can tell, "infinite clickthrough" refers to a feature of ReportBuilder when used in conjunction with Report Models on SSRS Enterprise whereby ReportBuilder will synthesize a clickthrough report on the fly, and has nothing to do with the ability to build "clickthrough" reports using links (the "Navigation" tab on the Properties of most any reporting services object).

View 1 Replies View Related

Infinite Loop In BOL Code!?

Apr 15, 2004

This code is from BOL (in index type: "DDLs-SQL Server"):
Take a look at error handling .. what happens if one of the three cmd.execute within the [Done:] hanlde fails?

It looks to me like we would have an infinite loop! .. am I missing something here?

BOL CODE EXAMPLE:
-----------------------------
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command

' If the ADOTestTable does not exist, go to AdoError.
On Error GoTo AdoError

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

' Set up command object.
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "DROP TABLE ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute

Done:
Cmd.CommandText = "SET NOCOUNT ON"
Cmd.Execute
Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub

AdoError:
Dim errLoop As Error
Dim strError As String

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next

GoTo Done

End Sub
-------------------------------------------------------

View 4 Replies View Related

TOP Solves Infinite Query ???

Oct 9, 2007

Hi There

I have a query that runs every night. It has been fine for months. Yesterday it started to run infinately.

When i execute it it never returns.

BUT when i add a top clause to othe query it works, now in my experience this normally happens because you are now eliminating the "bad" row with the top clause, so the join that causes it to run infinately now never occurs.

HOWEVER i run the exact query with a TOP 730 cluase and it only return 720 rows, so i am not eliminating any rows ?

Basically i have a query that runs infinately but if i add a top clause that does eliminate any rows it works ?

But if i increase the top cluase to 800 it runs inifnatley again, but it only returns 720 rows ?

I am stumped, can anyone shed some light on this please.


Thanx

View 10 Replies View Related

Infinite Loop In Cursor

Jul 5, 2006

Hi

I have an infinite loop in a trigger I and I cant reslove it.

In my system the user updates a stock table from the GUI and on the update I need to check values to see if I need to add records to a StockHistory table.  For Example:  If the user changes the grade of Product X from A to B then I need to add a new line in StockHistory for product X grade A that decrements the total number of products in the warehouse.  Similary I need to increase the quantity of stock for Product X grade B.

I had the trigger working for single updates but now when stock is added to the database (from another db) it has status of 'New'.  This isn't actually 'in stock' until the user sets the status to 'Goods In'.  This process will then update the status for all records in the category.  This caused my trigger to fail as the 'inserted' table now contains many records.

Now the problem I have is the trigger is in an infinite loop. It always shows the id of the first record it finds and the @Quantity values increases as expected.  I've taken all my procesing code out of the trigger and adding some debugging stuff but it still doesnt work:

CREATE TRIGGER [StockReturns_on_change] ON [dbo].[StockReturns]
FOR UPDATE
AS

DECLARE INDIVIDUAL Cursor --- Cursor for all the rows being updated

FOR
SELECT Id FROM inserted

OPEN INDIVIDUAL

FETCH NEXT FROM INDIVIDUAL INTO @Id

select @Quantity = 1

print @@FETCH_STATUS
print @Id
print @Quantity

WHILE @@FETCH_STATUS = 0
begin 

select @Quantity = @Quantity + 1

print @@FETCH_STATUS
print @Id
print @Quantity

-- Get the next row from the inserted table
FETCH NEXT FROM INDIVIDUAL INTO @Id

End  -- While loop on the cursor

-- no close off the cursors
CLOSE INDIVIDUAL
DEALLOCATE INDIVIDUAL





Can you help me please?

Kind Regards

View 13 Replies View Related

Cursor Causing Infinite Loop

Dec 13, 2007

Hi i have a cursor in a Stored Procedure. The problem is that it's poiting to the first row and causing an infinite loop on it.
How can i stop this and make it go to all rows. Here is my code.

Declare @CountTSCourtesy int
Declare @WaiterName nvarchar(100), @CursorRestaurantName nvarchar (100)
Declare waiter_cursor CURSOR FOR

SELECT new_waiteridname, new_restaurantname
FROM dbo.FilteredNew_CommentCard
Where new_dateofvisit between @FromDate and @ToDate and new_restaurantname = @Restaurant
Open waiter_cursor
FETCH NEXT FROM waiter_cursor
into @WaiterName,@CursorRestaurantName
While @@FETCH_STATUS=0

BEGIN
Exec WaitersCountExCourtesy @WaiterName,@CursorRestaurantName

END
Close waiter_cursor
Deallocate waiter_cursor
END


Thanks in advance...

View 1 Replies View Related

T-SQL (SS2K8) :: Simple Cursor Runs Infinite Loop?

Dec 23, 2014

I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...

IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable

[Code]....

View 2 Replies View Related

Executing SSIS Package Using Jobs In SQL Server Agent Infinite Loop

Jan 4, 2008

I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.

The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.

View 11 Replies View Related

DB Engine :: Trigger Jobs On Remote Server To Do Its Work On Original Server

May 10, 2015

I'm new to SQL. I have a scenario, Where customer want to move all the jobs from original SQL server to some remote SQL server and want to trigger jobs on remote server to do its work on original server.

View 4 Replies View Related

Recursive SQL

Feb 9, 2007

I need to help in writing stored procedure to recursively delete Categories and ads for those categories
Simplified table views:
Category: CategoryID, Name, ParentCategoryID
Ads: AdID, Name, CategoryID
 Please help

View 16 Replies View Related

How Can I Use Recursive CTE Here

Jun 1, 2007

 Hello Everyone,
         I have a purchase order table that holds say 2 columns. PO and OrgPO. That is Purchase Order # and Original Purchase Order # respectively. Assume i have the following rows in the table.
PO         OrgPO
--          ------
po1        NULL
co1        po1
co2        co1
co3        co2
po2        NULL
cpo1      po2
po3        NULL
Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg,
PO     LastCO
--      ------
po1     co3
po2     cpo1
po3     po3
Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.

View 2 Replies View Related

Recursive SQL

Jun 11, 2004

Hi

I have problem is getting list of all the Tree Level Employees.here is the my table structure


Manager ~ SubOrdinate
1 ~ 2
1 ~ 3
1 ~ 4
2 ~ 5
2 ~ 6
2 ~ 7
3 ~ 8
3 ~ 9
4 ~ 10
5 ~ 11
SO ON
I NEED THE WAY , HOW I CAN GET THE HIRE
LEVELS ( EG IF I PASS MANAGER(EMPLOYEE NO) 1 IT SHOULD DISPLAY HIS 1 LEVELS AND 2, 3 SO ON LEVELS OF LEVELS
OUTPUT COULD BE ,

PASSED EMPLOYEE NO : 1
LEVEL ~ EMPLOYEE
1 ~ 2
1 ~ 3
1 ~ 4
2.1 ~ 5
2.1 ~ 6
2.1 ~ 6
3.1 ~ 7
SO ON THAT EMPLOYEE RELATED INFORMATION
( ITS LIKE MLM LEVELS)
CAN ANYBODY HELP IN THE ,

THANKS

View 3 Replies View Related

Recursive Cte Within A Cte

May 12, 2006

is there any way to define a recursive cte within another cte. I have defined a recursive cte which works great for finding the different versions of a given item. What I would like to do is to define a set of items for which I want to find the different versions.

View 1 Replies View Related

How To Query The Using Recursive?

Jun 10, 2007

 
Hello,
I have the following tables:

Article(articleID,CategoryID,ArticleTitle)
Categories(categoryID,ParentID,CategoryTitle)
I am trying to retrieve the main category ID for a specific article ID.
For example lets say I have this data:
Article: 

1, 10 , "some title"
2,10,"some title"
3,11,"some title"
Categories:
1, NULL , "some title"
2, 1, "some title"
10, 2, "some title"
11, 10 , "some title"
 
In this example I want to know who is the main category of article 3.
The query should return the answer: 1
Thats because:

The article ID 3 is inside category 11.
Parent for category 11 is 10.
Parent for category 10 is 2.  
Parent for category 2 is 1
and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.
Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)
Do you have any suggestions for such query?
Thanks all.
 

View 1 Replies View Related

Recursive Query ..

Jan 18, 2008

Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project ..  .



I’m desperating due to the
complex (for me and also I think for some others) sql query that I need to
write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification
table that holds this mentioned “custom defined fieds� according to the Classifications
table, on where the Products can be found trought the productsClassifications
table.


CustomFields can be defined
and set for the products, trought his Classifications (instead of define a
custom field for each product (that consume a lot of data), I decide to use it as
I explain)

 

I will to know the properly
SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:

 


As example on
a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the
ProductsFields and Values that has the DBA for the:

·        
requested
ID_Classification

o       
PAL (ID_Classification: 16)

·        
AND all
the Classifications that belongs above (trought ID_ParentClassification) that are :

o       
Torrents (ID_Classification:
1) that will show the products values for the “Size�

o       
Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.

o       
Dreamcast (ID_Class..:14 )
that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)

 




Hmnn i show a graphic that i design for (feel to click over to see at correct resolution) 

 
 
 

I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com 
 

 

View 2 Replies View Related

Recursive Tsql

Feb 26, 2008

i have a table like this
parentid |    childid    |   description
 1             2                     blah
1              3
1              4
2              23
2              24
5              8
3              10
and i want to give the parentid  1  and get all the children
i have a cursor now like this but i dont know how to make it recursive any help?
 
 1
2 DECLARE @childid nvarchar(50)
3 DECLARE ItemStruc CURSOR FOR
4
5 SELECT cmponent_prt_no , parent_part_no
6 FROM oauser_prod_structure
7 WHERE parent_part_no = @parentid
8
9 OPEN ItemStruc
10 FETCH NEXT FROM ItemStruc
11 INTO @childNum , @parentid
12
13 WHILE @@FETCH_STATUS = 0
14 BEGIN
15
16 print @childNum +'is the a child to: ' +@parentid
 

View 12 Replies View Related

Recursive SQL Query

Sep 21, 2004

I have a tough issue with a query.

I have the following structure


Table: Users

UserId ParentUserId
1 1
2 1
3 2
4 1
5 4
6 5


I need to write a stored procedure that takes in UserId as parameter and returns everyone under him/her.

So if Param=1 it would return result set of 2,3,4,5,6

Anyone done this before or have any ideas?

Thanks,
ScAndal

View 1 Replies View Related

Recursive Query Help

Jul 20, 2005

Hi,

Does anyone know how to do an sql recursion queries?

I believe it involves a view with a union.

I have a User Table and in that table i have a employee_id and a
boss_id. What i'd like to do is to find all employees under a certain
boss.
For example,

Employee_ID Boss_ID
1
2                     1
3                    
4                     3
5                     2

So if i'd like to know who are under the employee_id = 1 it will return
employee_id 2 and 5 since employee 2 also is the boss of employee_id =
5.

To do that i'd have to have recursion query.

Thanks,

View 2 Replies View Related

More Recursive Questions

Oct 4, 2005

I have a list of categories that I have to find the path to each my table is set up like ID      CategoryNum        CategoryName    ParentID 1           AA                          Top Level 1           02            AB                          Top Level 2          03           BA                            Second Level 1     1I need my query/stored proc to return Tope Level 1/Second Level 1 if I pass in the integer of 3I can do this in programming but cant seem to wrap my head around it in SQL ServerTIA for the help

View 6 Replies View Related

Recursive Triggers

Dec 14, 2000

I'm using exec sp_dboption 'ilgadmin', 'recursive triggers',true

I made

create trigger
dbo.templates_ondelete
on templates for delete
as
begin
delete pages
where templateid = (select [id] from deleted)
end
go

create trigger
dbo.pages_ondelete
on pages for delete
as
begin
delete pageItems
where pageid = (select [id] from deleted)
end
go

The second trigger must be started by the first one.

But it doens't work because if I do
delete from templates where id = 2 more than one page (7 pages) are deleted and the second trigger doesn't work (it can only delete one by one in pageitems!)

Is there any option in SQL Server that I forgot or can I use an other methode

Best regards
Axel

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved