trigger on insert and update - SQL Server Forums

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.

All Forums

Old Forums

CLOSED - General SQL Server

trigger on insert and update

Author

Topic

jdattis
Starting Member

14 Posts

Posted - 2005-02-08 : 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

11752 Posts

Posted - 2005-02-08 : 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

14 Posts

Posted - 2005-02-08 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources