June 5, 2006 at 7:48 am
Hello Sir,
I have Two table "tbltgr" and "tblmparty" .structure of both table given below.I want cascade updation on both table
when i update txtParty_Name column of tblmparty table i want these changes also on tbltgr table at following columns<script></script>
tbltgr.txtgr_Consignor,tbltgr.txtgr_consignee,
tbltgr.txtgr_billparty . plz mail to me solutions
CREATE TABLE [tbltGR] (
[txtgr_no] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[datgr_date] [smalldatetime] NULL ,
[datgr_Entrydate] [smalldatetime] NULL ,
[txtgr_Consignor] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_consignee] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_routeFrom] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_routeTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intgr_qty] [int] NULL ,
[txtgr_Material] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_Materialname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[datgr_InvDate] [smalldatetime] NULL ,
[txtgr_Invno] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intgr_InvValue] [int] NULL ,
[txtgr_Remarks] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_billBranch] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_billparty] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_FreightMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_BookingMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fltgr_ActualWt] [decimal](18, 0) NULL ,
[fltgr_Chargedwt] [decimal](18, 0) NULL ,
[fltgr_Rate] [decimal](18, 0) NULL ,
[fltgr_FreightCharge] [decimal](18, 0) NULL ,
[fltgr_StatisticalCharge] [decimal](18, 0) NULL ,
[fltgr_CollectionCharge] [decimal](18, 0) NULL ,
[fltgr_DeliveryCharge] [decimal](18, 0) NULL ,
[fltgr_LogisticCharge] [decimal](18, 0) NULL ,
[fltgr_ServiceTax] [decimal](18, 0) NULL ,
[fltgr_OctroiCharge] [decimal](18, 0) NULL ,
[fltgr_OctSerCharge] [decimal](18, 0) NULL ,
[fltgr_DemurrageCharge] [decimal](18, 0) NULL ,
[fltgr_OtherCharge] [decimal](18, 0) NULL ,
[fltgr_TotalAmt] [decimal](18, 0) NULL ,<script></script>
[intgr_LoadingBranch] [int] NULL ,
[txtgr_Grtype] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_Canclegr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_vehno] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_billno] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtgr_DelveryPoint] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tbltGR] PRIMARY KEY CLUSTERED
(
[txtgr_no]
) ON [PRIMARY] ,
CONSTRAINT [FK_tbltGR_tblmParty] FOREIGN KEY
(
[txtgr_billparty]
) REFERENCES [tblmParty] (
[txtParty_Name]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_tbltGR_tblmPlace] FOREIGN KEY
(
[txtgr_routeTo]
) REFERENCES [tblmPlace] (
[txtpl_Place]
),
CONSTRAINT [FK_tbltGR_tblsPlace] FOREIGN KEY
(
[txtgr_routeFrom]
) REFERENCES [tblmPlace] (
[txtpl_Place]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_tbltGR_tbltGRBilling] FOREIGN KEY
(
[txtgr_billno]
) REFERENCES [tbltGRBilling] (
[txtBill_No]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_tbltGR_txtgrConsignor
(
[txtgr_Consignor]
) REFERENCES [tblmParty] (
[txtParty_Name]
)
) ON [PRIMARY]
GO
--------------
CREATE TABLE [tblmParty] (
[txtParty_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[txtparty_Address1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtparty_Address2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtparty_PhoneNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[txtParty_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtparty_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblmParty] PRIMARY KEY CLUSTERED
(
[txtParty_Name]
) ON [PRIMARY] ,
CONSTRAINT [UK_tblmParty] UNIQUE NONCLUSTERED
(
[txtparty_PhoneNo]
) ON [PRIMARY]
) ON [PRIMARY]<script></script>
GO
June 5, 2006 at 12:42 pm
Hi Ranjeet,
I shall leave answers to the experts, except to mention that your problem seems to stem from your table structures.
I think, if you correctly identify your entities/elements and normalise it a bit, your problem should go away.
Good luck,
Ian
June 5, 2006 at 10:11 pm
Hi
Thanks to leave my problem to expert.
June 6, 2006 at 12:12 am
You can use Triggers (update triggers) for the same. Refer BOL for detailed syntax.
Cheers!
June 6, 2006 at 7:33 am
I use trigger and solve cascade update problem but there are to many trigger in my Database .And almost every table of my database has trigger .
will parformance not effected of DataBase.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply