trigger - 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

General SQL Server Forums

New to SQL Server Programming

trigger

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]
)

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

52326 Posts

Posted - 2013-06-18 : 07:35:22

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

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 updated

jaffer 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 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

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]
)

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources