Instead of UPdate triggers - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Instead of UPdate triggers

Author

Topic

Danny4003
Starting Member

40 Posts

Posted - 2007-01-16 : 14:48:38

When I create the Instead of Update Trigger.... Do I create it
on the table that i will be updating?

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-16 : 14:58:19

quote:
Originally posted by Danny4003

When I create the Instead of Update Trigger.... Do I create it
on the table that i will be updating?

Yes

if you want more help, we will need more info

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Danny4003
Starting Member

40 Posts

Posted - 2007-01-16 : 15:23:44

CREATE TABLE [TractsTbl] (
[TractID] [int] PRIMARY KEY NOT NULL ,
[TractNumber] [int] IDENTITY(1000,1000)NOT NULL ,
[Acreage] [numeric](12, 3) NULL ,
[Location] [varchar](50) NOT NULL ,
[County] [varchar] (50) NULL ,
[State] [char](2) NULL ,
[Description] [text] NULL ,
[TractImg] [varchar](25) NULL ,
[TractCount] [int] NULL ,
[Status] [varchar](25) NULL ,
[LocationType] [varchar] (50) NULL
--CONSTRAINT Status_FK1 FOREIGN KEY(Status) REFERENCES StatusTbl (StatusID),
--CONSTRAINT State_FK2 FOREIGN KEY (State) REFERENCES StatesTbl (StatesID),
)

--CREATE BEFORE (LOOKUP TABLE)
CREATE TABLE [StatusTbl] (
[StatusID] [int] PRIMARY KEY IDENTITY(1,1)NOT NULL ,
[Status] [varchar] (25) NOT NULL
)

INSERT INTO StatusTbl VALUES('Open')
INSERT INTO StatusTbl VALUES('Closed')
INSERT INTO StatusTbl VALUES('Limbo')
SELECT * FROM StatusTbl
-----------------------------------------

CREATE TABLE [StatesTbl] (
[StatesID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[States] [varchar] (25) NOT NULL
)
INSERT INTO StatesTbl VALUES('Nebraska')
INSERT INTO StatesTbl VALUES('Alabama')
INSERT INTO StatesTbl VALUES('Texas')
INSERT INTO StatesTbl VALUES('Oklahoma')
select * from StatesTbl

CREATE TABLE [ProjectTbl] (
[ProjectID] [int] Primary Key,
[ProjectNumber] [int]IDENTITY(12356,12356) NOT NULL ,
[TractID] [int] NULL
FOREIGN KEY REFERENCES TractsTbl(TractID)
ON UPDATE CASCADE,
[ProjectName] [varchar] (50) NULL ,
[County_Parish] [varchar] (50) NULL ,
[State] [char](2) NULL ,
--FOREIGN KEY REFERENCES StatesTbl(StatesID) ,
[County_Parish_Seat] [varchar] (50) NULL ,
[Est_Totlal_Acres] [numeric](12, 3) NULL ,
[Project_Start_Date] [datetime] NULL ,
[Project_Billing_Date] [datetime] NULL ,
[Status] [varchar](25) NULL,
--FOREIGN KEY REFERENCES StatusTbl (StatusID),
[AccountNumber] [varchar] (50) NULL
)

--------------------------------------------
--LOOKUP TABLE
CREATE TABLE [OwnerType] (
[OwnTypeID] [int]PRIMARY KEY IDENTITY (1,1) NOT NULL ,
[OwnType] [varchar] (50) NULL
)
INSERT INTO OwnerType Values('LandMan')
INSERT INTO OwnerType Values('Accountant')
INSERT INTO OwnerType Values('Inspector')
INSERT INTO OwnerType Values('Boss Man')
Select * from OwnerType
----------------------------------------------

CREATE TABLE [OwnersTbl] (
[OwnerID] [int]PRIMARY KEY IDENTITY(100000,100000) NOT NULL ,
[ProjectID][int]Null
FOREIGN KEY REFERENCES ProjectTbl(ProjectID)
ON UPDATE CASCADE,
[OwnerType] [varchar](25) NULL ,
[TractID] [int] NULL
FOREIGN KEY REFERENCES TractsTbl(TractID)
ON UPDATE CASCADE,
[Name] [varchar] (50) NULL ,
[Spouse] [varchar] (50) NULL ,
[Address] [varchar](255) NULL ,
[PhoneNumber] [char](25) NULL ,
[Comments] [text] NULL,
--CONSTRAINT OwnerType_FK1 FOREIGN KEY (OwnerType)REFERENCES OwnerType (OwnTypeID)
)

INSERT INTO ProjectTbl Values(1,'TestProj1','Jasper','3','Jasper Seat','123.545','12/12/1992','12/25/2000','3','123456789')
INSERT INTO ProjectTbl Values(2,'TestProj2','Alabama','2','Alabama Seat','321.454','05/05/2002','05/25/2002','2','321654987')
INSERT INTO ProjectTbl Values(3,'TestProj3','Oklahoma','4','Okla Seat','546.321','02/02/2003','02/25/2003','1','789456123')
INSERT INTO ProjectTbl Values(4,'TestProj4','Corn Husker','1','Neb Seat','458.213','09/20/2004','09/30/2004','3','12546587')
INSERT INTO ProjectTbl Values(5,'TestProj5','Corn Husker','1','Neb Seat','458.213','09/20/2004','09/30/2004','3','956874612')
INSERT INTO ProjectTbl Values(6,'TestProj5','Corn Husker','1','Neb Seat','458.213','09/20/2004','09/30/2004','3','555654587')
INSERT INTO ProjectTbl Values(1,'TestProj6','Alabama','2','Alabama Seat','321.454','09/20/2004','09/30/2004','3','254657745')
INSERT INTO ProjectTbl Values(1,'TestProj7','Alabama','2','Alabama Seat','321.454','09/20/2004','09/30/2004','3','254657745')
INSERT INTO ProjectTbl Values(1,'TestProj8','Alabama','2','Alabama Seat','321.454','09/20/2004','09/30/2004','3','254657745')

Select * from TractsTbl --Order By TractImg ASC

INSERT INTO TractsTbl Values('1','273.161','Oklahoma','Oklahoma','4','This Tract has 50% of mineral to Tot.Acres','14','2','1','N/A')
INSERT INTO TractsTbl Values('2','273.161','Oklahoma','Oklahoma','4','This Tract has the other 50% to ToT.Acres','12','2','1','N/A')
INSERT INTO TractsTbl Values('3','458.213','Nebraska','Corn Husker','1','100% belongs to this Tract','10','1','3','N/A')
INSERT INTO TractsTbl Values('4','321.454','Alabama','Alabama','2','100% belongs to this Tract','2','1','2','N/A')
INSERT INTO TractsTbl Values('5','41.181','Texas','Jasper','3','75% belongs to this Tract','6','3','3','N/A')
INSERT INTO TractsTbl Values('6','41.181','Texas','Jasper','3','75% belongs to this Tract','8','3','3','N/A')
INSERT INTO TractsTbl Values('7','41.181','Texas','Jasper','3','75% belongs to this Tract','4','3','3','N/A')

SELECT * FROM OWNERSTBL
Select * from TractsTbl
Select * from ProjectTbl
INSERT INTO OwnersTbl Values('10000','1','1','Dave Trahan','N/A','312B Knollwood Dr. Lafayette,La 70506','337-346-5566','This is the LandMan for this Tract')
INSERT INTO OwnersTbl Values('20000','2','2','Jarred Landry','N/A','154 Circle Rd. Dallas,TX 54668','504-258-9689','This is the Boss of the LandMan for this Tract')
INSERT INTO OwnersTbl Values('30000','3','3','Danny Dubroc','Constance Dubroc','1065 Ellen Rd. Ville Platte,La 70586','337-290-0880','This is the Boss of 100% for this Tract')
INSERT INTO OwnersTbl Values('40000','4','4','JoJo Lodrigue','N/A','254 Ballon Dr. Port Barre,La 70508','337-564-5456','This Boss owns 100% of this Tract')
INSERT INTO OwnersTbl Values('20000','1','5','Todd Newbanks','Jackie Newbanks','215 Lexman Dr. Opelousas,La 70570','337-564-2525','This LandMan owns 75% of this Tract')
INSERT INTO OwnersTbl Values('30000','2','6','Jack Harbour','N/A','222 Jake Dr. Jasper,TX 54667','504-856-8080','This Accountant owns 75% of this Tract')
INSERT INTO OwnersTbl Values('10000','3','7','Nick Orwell','Melissa Orwell','54 Tom Green,TX 54665','504-956-5454','This Inspector owns the last 75% of this Tract')

I want to create a Trigger to update the three table via the TractID

Please help if you can?

Thanks in Advance
Danny

Danny4003
Starting Member

40 Posts

Posted - 2007-01-16 : 15:40:41

TractsTbl
OwnersTbl
ProjectsTbl

Sorry forgot to put that
Danny

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-16 : 15:42:03

OK, but to do what?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Danny4003
Starting Member

40 Posts

Posted - 2007-01-16 : 15:46:55

I want to create an Update Statement that updates all Three tables and i am at a lost with this one can you help?

Thanks
Danny