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.
| Author |
Topic |
|
das
Starting Member
8 Posts |
 Posted - 2003-04-09 : 09:16:54
|
| I trying to create a delete stored proc that will delete all order headers when they reach a certain status. There are 2 order header tables, each have a status associated with them. When the order is orgrinally enetered it has 1 header but when detail line items are added it can cause the generation of 1 or many headers in new table. After orders are posted they need deleted. The problem is that there is a foreign key between the 2 tables. So I cant delete the the original header until all newly created headers reach a posted status.Ex: Orig HeaderID status1 1Secondary HeadersNew ID Old ID status 1 1 1 2 2 2I need to deleted orig header when both secondary headers have a status of 2.Any help would be appreciated. ThanksDDLdrop table tblStageOrderHeaderDSdrop table tblOrderHeaderDSCREATE TABLE tblStageOrderHeaderDS ( [intOrderID] [int] IDENTITY (1, 1) NOT NULL , [strLocationID] [varchar] (4) NOT NULL , [intStatus] int CONSTRAINT [PK_tblStageOrderHeaderDS] PRIMARY KEY CLUSTERED ( [intOrderID] ) ON [PRIMARY] )CREATE TABLE tblOrderHeaderDS ( [strLocationID] [varchar] (4) NOT NULL , [intOrderID] [int] IDENTITY (1, 1) NOT NULL , [intStageOrderID] [int] NOT NULL , [intStatus] int CONSTRAINT [PK__tblOrderHeader__DS] PRIMARY KEY NONCLUSTERED ( [intOrderID] ) ON [PRIMARY] , CONSTRAINT [FK_tblOrderHeader_tblStageOrderHeaderDS] FOREIGN KEY ( [intStageOrderID] ) REFERENCES [tblStageOrderHeaderDS] ( [intOrderID] ))insert tblStageOrderHeaderDS(strLocationID,intStatus)values ('0811',1)insert tblOrderHeaderDS(strLocationID,intStageOrderID,intStatus)values ('0811',1,1)insert tblOrderHeaderDS(strLocationID,intStageOrderID,intStatus)values ('0811',1,2)insert tblOrderHeaderDS(strLocationID,intStageOrderID,intStatus)values ('0811',1,2) |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-09 : 09:26:27
|
quote:
when they reach a certain status
My first question is:What event tells you that you have attained such a goal?If you can answer that, you can probably "trigger" an action to do what you want.Let us know.Brett8-)Edited by - x002548 on 04/09/2003 09:27:03 |
 |
|
|
das
Starting Member
8 Posts |
Posted - 2003-04-09 : 12:20:21
|
| After orders are entered an process trigger by user through a click of a button verifies the orders. This process create all of the new headers. Then the user can post 1 or many orders into into a mainframe order entry system. Once they are posted there is no use for them. I would like the user to delete his orders when he/she wants so I have a button in application that will execute this stored proc to clean up there records. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-09 : 13:14:21
|
quote:
I would like the user to delete his orders when he/she wants so I have a button in application
What makes you think they will click the button to perform the delete? Just because they will be good little users?Also I'm not following, if you know the order id, can't you just delete them once they are posted?I'm confused.Brett8-) |
|
|
|
das
Starting Member
8 Posts |
Posted - 2003-04-09 : 13:24:13
|
| They will deleted their orders because the is the main web page when they login the system so if they retrieve 3000 records and page takes forever to load they delete the orders. You are right I could just delete after posting but they will probably keep for a couple of days in case customer calls looking for their order. Again on my exampleI could delete the orginal order when data is like below. When the post the #2 order then I can delete Orig order header.Ex: Orig Header ID status 1 1 Secondary Headers New ID Old ID status 1 1 1 2 2 2 |
|
|
|
|
|
|
|
|
Deleting Records