May 27, 2011 at 5:07 am
Hello,
I have two table with name Eng_Details (Master Table) and Eng_Edu_Details (Child Table). When I make a changes in master table it will changes the insert record date in both table. Means Curr_Date filed in both table are changes when makes a changes in Master table.
But the problem is that when i makes a changes in Child Table( EnG_EDU_Details) then date is changes only in Child Table not in Master table. Any Idea how to do it????
The Structure of Both Table are given Below....
Master Table (Parent Table)
CREATE TABLE [dbo].[Engineers_Details](
[Eng_ID] [int] IDENTITY(1,1) NOT NULL,
[Curr_Date] [varchar](20) NULL,
[Eng_Ref] [varchar](15) NULL,
[Email] [varchar](50) NOT NULL,
[Password] [varchar](50) NOT NULL,
[EngTitle] [varchar](50) NOT NULL,
[EngFirst] [varchar](50) NOT NULL,
[EngMiddle] [varchar](30) NOT NULL,
[EngLast] [varchar](50) NOT NULL,
[EngDob] [varchar](20) NOT NULL,
[Gender] [varchar](50) NOT NULL,
[EngCountry] [varchar](50) NOT NULL,
[EngState] [varchar](50) NOT NULL,
[EngCity] [varchar](50) NOT NULL,
[EngZipPostal] [varchar](50) NOT NULL,
[EngMobile] [varchar](50) NOT NULL,
[EngTelephone] [varchar](50) NULL,
[EngResume] [varchar](2000) NOT NULL,
[EngDesirdJobTitle] [varchar](50) NOT NULL,
[EngJobType] [varchar](50) NOT NULL,
[EngIndustry] [varchar](50) NOT NULL,
[EngKeySkill] [varchar](50) NOT NULL,
CONSTRAINT [PK_Engineers_Details] PRIMARY KEY CLUSTERED
(
[Eng_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Structure of Child Table....
CREATE TABLE [dbo].[Eng_Edu_Details](
[Eng_Edu_Id] [int] IDENTITY(1,1) NOT NULL,
[Eng_Id] [int] NULL,
[Eng_Grad_Name] [varchar](25) NULL,
[Eng_Grad_Decipline] [varchar](25) NULL,
[Eng_Course_Type_Grad] [varchar](20) NULL,
[Eng_Grad_University] [varchar](50) NULL,
[Eng_Grad_Percentage] [float] NULL,
[Eng_Grad_Year] [int] NULL,
[Eng_Post_Grad_Name] [varchar](25) NULL,
[Eng_Post_Decipline] [varchar](25) NULL,
[Eng_Post_Type] [varchar](20) NULL,
[Eng_Post_University] [varchar](50) NULL,
[Eng_Post_Percentage] [float] NULL,
[Eng_Post_Year] [int] NULL,
[Eng_Other_Course] [varchar](50) NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Eng_Edu_Details] ADD [Curr_Date] [varchar](20) NULL
/****** Object: Index [PK_Eng_Edu_Details] Script Date: 05/27/2011 16:33:29 ******/
ALTER TABLE [dbo].[Eng_Edu_Details] ADD CONSTRAINT [PK_Eng_Edu_Details] PRIMARY KEY CLUSTERED
(
[Eng_Edu_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
May 27, 2011 at 5:55 am
Modify the procedure that updates the child table so that it also updates the parent table. That'd be my first approach. You could also look at setting up a trigger so that when data gets updated it in the child it automatically updates the parent. I'm not a fan of triggers because of their hidden nature, but it's a viable approach.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 27, 2011 at 6:02 am
I have create a trigger... but it is not working let me know how to do it?
create trigger tgr_change_master_Date on dbo.Eng_Education_Details
for update a
set Curr_Date=GetDate()
from dbo.Engineers_Details a
inner join inserted i
on a.Eng_id=i.Eng_Id
.....
When I try it in asp.net its not working.... bec Master and child table has primary and foreign key relation that allow any changes from parent to child not from child to parent .
Here Eng_Edu_Details is child of Engineers_Details (a Master table or Parent table)
Any idea how to change the date when make any data changes or insert in Child Table?
May 27, 2011 at 6:19 am
Your trigger needs to be on the child table and it needs to say "UPDATE Parentable..." in order for the parent table to be updated.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 27, 2011 at 6:31 am
How to do it?
Can you give any sample code for it?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply