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.
Author |
Topic |
jafar
Starting Member
2 Posts |
Posted - 2013-06-18 : 07:27:29
|
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])table2CREATE 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 updatedjaffer sadik |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 07:35:22
|
you cant create a trigger based on two tablescan 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jafar
Starting Member
2 Posts |
Posted - 2013-06-18 : 07:40:08
|
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 updatedjaffer sadik |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2013-06-18 : 07:46:02
|
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/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 07:50:27
|
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 updatedjaffer sadik
so is the condition likeonly 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-18 : 08:32:38
|
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])table2CREATE 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 updatedjaffer 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.SearchPAS p.pid, ps.name, ps.docstatus, ps.desease, ps.bedno, ps.drugs, d.docnameFROM dbo.patient p LEFT JOIN dbo.pstatus ps ON ps.pid = p.pid LEFT JOIN dbo.doctors d ON d.docname = p.docname; |
|
|
|
|
|
|
|