How to approach (Trigger-Stored procedure )

[Alvin]

Hi All

I need opinions on how to approach my task.
I currently have 3 tables: the master table, the archive and a temp
table.
MASTER: has 3 fields ProductID and ProductNo and Released
ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
TEMP: ProductID, ProductNo, SoldDate

I have a trigger on the master table upon deletion to archive. This is
triggered from a seperate routine from a vb app to delete a record
real time.

CREATE Trigger Archive_Proc On dbo.MASTER
For Delete
As
Declare @iDate As DateTime
Set @iDate = GetDate()

If @@RowCount = 0 Return
set Nocount on
Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select ProductID, ProductNo, @iDate from deleted
My problem is that I have a temp table that gets filled from a
seperate transaction.It needs to be matched against the master table
then deleted at both master and temp. but the issue is that the temp
table contains its own SoldDate value that needs to be archived.

Q 1: if I use a stored proc. how do i pass the SoldDate value to the
trigger as Triggers dont use GVs.
Q 2: How do I set up the stored procedure to delete with multiple
tables. I can get it to UPDATE but not delete....

CREATE PROCEDURE COMPARESOLD
@Pool Smallint
AS
Set NoCount on

Update MASTER
Set Released = 2
From TEMP, MASTER
Where TEMP.ProductNo = MASTER.ProductNo
AND TEMP.ProductID = MASTER.ProductID
AND INVENTORY.Released = 1

hopefully someone can lead me to the right direction...
Thanks

[John Bell]

Hi

I have not picked up the exact relationship between temp and the Master
table. Posing correct DDL (using the scripting options in Enterprise Manager
or the QA Object browser) and example data as inserts statements go a long
way to remove this sort of ambiguity.

It seems that you should have a FK from the TEMP table to the Master that
cascades on delete.

It may also be possible to change the trigger so that it picks up the
SoldDate from TEMP such as:

Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select d.ProductID, d.ProductNo, ISNULL(t.SoldDate,@iDate)
from deleted d LEFT JOIN Temp t on d.ProductID = t.ProductID

You may want something slightly different if there are multiple TEMP
records.

John

"Alvin" <jo*********@earthlink.net> wrote in message
news:cc**************************@posting.google.c om...

Hi All

I need opinions on how to approach my task.
I currently have 3 tables: the master table, the archive and a temp
table.
MASTER: has 3 fields ProductID and ProductNo and Released
ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
TEMP: ProductID, ProductNo, SoldDate

I have a trigger on the master table upon deletion to archive. This is
triggered from a seperate routine from a vb app to delete a record
real time.

CREATE Trigger Archive_Proc On dbo.MASTER
For Delete
As
Declare @iDate As DateTime
Set @iDate = GetDate()

If @@RowCount = 0 Return
set Nocount on
Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select ProductID, ProductNo, @iDate from deleted
My problem is that I have a temp table that gets filled from a
seperate transaction.It needs to be matched against the master table
then deleted at both master and temp. but the issue is that the temp
table contains its own SoldDate value that needs to be archived.

Q 1: if I use a stored proc. how do i pass the SoldDate value to the
trigger as Triggers dont use GVs.
Q 2: How do I set up the stored procedure to delete with multiple
tables. I can get it to UPDATE but not delete....

CREATE PROCEDURE COMPARESOLD
@Pool Smallint
AS
Set NoCount on

Update MASTER
Set Released = 2
From TEMP, MASTER
Where TEMP.ProductNo = MASTER.ProductNo
AND TEMP.ProductID = MASTER.ProductID
AND INVENTORY.Released = 1

hopefully someone can lead me to the right direction...
Thanks