| Author |
 Topic  |
|
|
jdattis
Starting Member
USA
14 Posts |
 Posted - 02/08/2005 : 14:09:30
|
Overview:
tblPieceTemp - flat file imported into here
tblPiece - real table read from front end
tblSite - contains sites
tblPieceSites - tells me what pieces are on what sites, pretty basic so far.
Flat file has 2 columns I am concerned with here. One of those columns determines if a piece shows up on Site A, another column determines if a piece shows up on ALL of the other sites. This is coming from a Progress 6.2 DB on Unix and cannot be changed so I must deal with it.
I import all the column into tblPieceTemp, then transform the columns and either insert/update/delete from tblPiece. Delete really just marks a record as deleted, never really deletes the row.
In tblPieceSite I have 4 fields, PieceID, SiteID, Unix, UnixOverride.
I would like my INSERT and UPDATE triggers to update the Unix column in tblPieceSite. There should be a record fo EVERY Piece/Site combination in this table regardless of the value in Unix and UnixOverride. The Unix field is set by the import and the UnixOverride is set by my web front end.
Firstly, does this make sense?
Secondly, would anyone like to see my sad attempt at the triggers?
Thanks in advance,
JDA |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 02/08/2005 : 14:11:57
|
something like this??
insert into tblPieceSite( Piece, Site)
select t1.Piece, t2.Site
from inserted t1 cross join inserted t2
Go with the flow & have fun! Else fight the flow  |
 |
|
|
jdattis
Starting Member
USA
14 Posts |
Posted - 02/08/2005 : 14:18:29
|
my insert looks like this:
CREATE TRIGGER [trPieceSiteInsert] ON [dbo].[tblPiece]
FOR INSERT
AS
--tblPieceSite insert for site1
DECLARE @SiteIDsite1 int
SELECT
@SiteIDsite1 = SiteID
FROM tblSite
WHERE Shortname = 'site1'
INSERT INTO tblPieceSite
(
PieceID,
SiteID,
Unix
)
SELECT
INSERTED.PieceID,
@SiteIDsite1,
tblPieceTemp.WebDisplaySite1
FROM INSERTED
INNER JOIN tblPieceTemp
ON tblPieceTemp.PieceNumber = INSERTED.PieceNumber
--tblPieceSite insert for site2
DECLARE @SiteIDsite2 int
SELECT
@SiteIDsite2 = SiteID
FROM tblSite
WHERE Shortname = 'Site2'
INSERT INTO tblPieceSite
(
PieceID,
SiteID,
Unix
)
SELECT
INSERTED.PieceID,
@SiteIDsite2,
tblPieceTemp.WebDisplaySite2
FROM INSERTED
INNER JOIN tblPieceTemp
ON tblPieceTemp.PieceNumber = INSERTED.PieceNumber
my update looks like this and there is definetly something wrong with it:
CREATE TRIGGER [trPieceSiteUpdate] ON [dbo].[tblPiece]
FOR UPDATE
AS
--tblPieceSite update for site1
DECLARE @SiteIDsite1 int
SELECT
@SiteIDsite1 = SiteID
FROM tblSite
WHERE Shortname = 'site1'
UPDATE tblPieceSite
SET
Unix = tblPieceTemp.WebDisplaySite1
FROM INSERTED
INNER JOIN tblPieceTemp
ON tblPieceTemp.PieceNumber = INSERTED.PieceNumber
WHERE tblPieceSite.SiteID = @SiteIDsite1
--tblPieceSite update for site2
DECLARE @SiteIDsite2 int
SELECT
@SiteIDsite2 = SiteID
FROM tblSite
WHERE Shortname = 'site2'
UPDATE tblPieceSite
SET
Unix = tblPieceTemp.WebDisplaysite2
FROM INSERTED
INNER JOIN tblPieceTemp
ON tblPieceTemp.PieceNumber = INSERTED.PieceNumber
WHERE tblPieceSite.SiteID = @SiteIDsite2
Anyone see the problem. Also, could this be written in such a way that I would not have to update it when i add a site.
Thanks,
JDA
|
|
|
| |
Topic |
|
|
|
trigger on insert and update
Forum Locked
