SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 trigger on insert and update
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

jdattis
Starting Member

USA
14 Posts

Posted - 02/08/2005 :  14:09:30  Show Profile
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
11751 Posts

Posted - 02/08/2005 :  14:11:57  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

jdattis
Starting Member

USA
14 Posts

Posted - 02/08/2005 :  14:18:29  Show Profile
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




Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000