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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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