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
 General SQL Server Forums
 New to SQL Server Programming
 trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jafar
Starting Member

India
2 Posts

Posted - 06/18/2013 :  07:27:29  Show Profile  Reply with Quote
CREATE TABLE [dbo].[patient](
[pid] [nvarchar](50) NOT NULL,
[name] [varchar](50) NOT NULL,
[email] [varchar](50) NOT NULL,
[password] [varchar](50) NOT NULL,
[createdon] [datetime] NOT NULL,
[modifiedon] [datetime] NOT NULL,
[rowstate] [tinyint] NOT NULL,
[Address] [varchar](100) NULL,
[dob] [datetime] NULL,
[phone] [varchar](12) NULL,
CONSTRAINT [PK_patient] PRIMARY KEY CLUSTERED
(
[pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

table2

CREATE TABLE [dbo].[pstatus](
[pstatus] [varchar](50) NOT NULL,
[docstatus] [varchar](50) NOT NULL,
[disease] [varchar](50) NOT NULL,
[bedno] [varchar](3) NOT NULL,
[drugs] [varchar](50) NULL
) ON [PRIMARY]

new table

CREATE TABLE [dbo].[searchp](
[pid] [nvarchar](50) NOT NULL,
[name] [varchar](50) NOT NULL,
[docstatus] [varchar](50) NOT NULL,
[disease] [varchar](50) NOT NULL,
[bedno] [varchar](3) NOT NULL,
[drugs] [varchar](50) NULL,
[docname] [varchar](50) NOT NULL
) ON [PRIMARY]


i want to create a trigger when the values are inserted into the first 2 tables the new table will be automatically updated

jaffer sadik

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  07:35:22  Show Profile  Reply with Quote
you cant create a trigger based on two tables
can you elaborate on requirement a bit? whats the sequence in which insertion occurs?
is it patient followed by table2 or reverse? will there always be simultaneous inserts in both tables?
is there a chance of data getting into one of the tables without affecting the other?
how do you ensure atomicity in those two insert operations? does it always take place inside a single transaction? or is there another related trigger?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jafar
Starting Member

India
2 Posts

Posted - 06/18/2013 :  07:40:08  Show Profile  Reply with Quote
in my database there are 2 tables 'doctor' and 'patient' n i want one more table that is combination of doctor and patient so when the values of doctor and patient are inserted the new table will be automatically updated

jaffer sadik
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/18/2013 :  07:46:02  Show Profile  Visit ditch's Homepage  Reply with Quote
Like Vikash said, this can't be done based on 2 tables, you can catch it in one table and in the trigger code join the 2 tables. or you can handle all inserts from a stored procedure. I personally will opt for the stored procedure route.


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  07:50:27  Show Profile  Reply with Quote
quote:
Originally posted by jafar

in my database there are 2 tables 'doctor' and 'patient' n i want one more table that is combination of doctor and patient so when the values of doctor and patient are inserted the new table will be automatically updated

jaffer sadik


so is the condition like

only if both tables have data inserted you need to handle them?

what about cases were you've new doctor but no patients yet?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 06/18/2013 :  08:32:38  Show Profile  Reply with Quote
quote:
Originally posted by jafar

CREATE TABLE [dbo].[patient](
[pid] [nvarchar](50) NOT NULL,
[name] [varchar](50) NOT NULL,
[email] [varchar](50) NOT NULL,
[password] [varchar](50) NOT NULL,
[createdon] [datetime] NOT NULL,
[modifiedon] [datetime] NOT NULL,
[rowstate] [tinyint] NOT NULL,
[Address] [varchar](100) NULL,
[dob] [datetime] NULL,
[phone] [varchar](12) NULL,
CONSTRAINT [PK_patient] PRIMARY KEY CLUSTERED
(
[pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

table2

CREATE TABLE [dbo].[pstatus](
[pstatus] [varchar](50) NOT NULL,
[docstatus] [varchar](50) NOT NULL,
[disease] [varchar](50) NOT NULL,
[bedno] [varchar](3) NOT NULL,
[drugs] [varchar](50) NULL
) ON [PRIMARY]

new table

CREATE TABLE [dbo].[searchp](
[pid] [nvarchar](50) NOT NULL,
[name] [varchar](50) NOT NULL,
[docstatus] [varchar](50) NOT NULL,
[disease] [varchar](50) NOT NULL,
[bedno] [varchar](3) NOT NULL,
[drugs] [varchar](50) NULL,
[docname] [varchar](50) NOT NULL
) ON [PRIMARY]


i want to create a trigger when the values are inserted into the first 2 tables the new table will be automatically updated

jaffer sadik

You rtables don't seem to be designed to relate the appropriate entities - for example, in the pstatus table, I don't see any column that will tell you which patient a given row refefs to. So you would need a pid column in that table.

Also, where does docname column in the searchp table come from? Is there another table that lists doctors?

It may be that all you need is a view, rather than a base table. For example, something like shown below - where I am making quite a few assumptions, so this is something just get you started:
CREATE VIEW dbo.SearchP
AS
	p.pid,
	ps.name,
	ps.docstatus,
	ps.desease,
	ps.bedno,
	ps.drugs,
	d.docname
FROM
	dbo.patient p
	LEFT JOIN dbo.pstatus ps
		ON ps.pid = p.pid
	LEFT JOIN dbo.doctors d
		ON d.docname = p.docname;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 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