need help as soon as possible

Carnation Flowers

9y867

i create database and all relations

i have Session table

USE [OnCall]

/****** Object: Table [dbo].[Sessions] Script Date: 03/14/2017 21:08:26 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Sessions](

[SessionID] [int] NOT NULL,

[PatFileID] [int] NULL,

[DocID] [int] NULL,

[Date] [datetime] NULL,

[PickupOfvistLocation] [nvarchar](max) NULL,

[SessionFees] [decimal](18, 0) NULL,

[MCFees] [decimal](18, 0) NULL,

[DoctorFees] [decimal](18, 0) NULL,

[AppFees] [decimal](18, 0) NULL,

[PeriodOfsession] [nvarchar](50) NULL,

[DescriptionOfSession] [nvarchar](max) NULL,

[LabTestID] [nvarchar](50) NULL,

[XaryTestID] [nvarchar](50) NULL,

[MedisionName] [nvarchar](50) NULL,

[InjuctionName] [nvarchar](50) NULL,

[Others] [nvarchar](max) NULL,

[NotesAr] [nvarchar](max) NULL,

[NotesEng] [nvarchar](max) NULL,

[NumOfVisit] [int] NULL,

CONSTRAINT [PK_Sessions] PRIMARY KEY CLUSTERED

(

[SessionID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[Sessions] ADD CONSTRAINT [DF_Sessions_NumOfVisit] DEFAULT ((1)) FOR [NumOfVisit]

------------------

be side it i have rate table

MCID int

Rate Decimal

----------------------

what i need??

seeions table has these fields

[SessionFees]

[MCFees]

[DoctorFees]

[AppFees]

wanna when user enter seeion fees the other three columns be auto compouted

like this

[MCFees]=[SessionFees]*Rate from table rate/100

[DoctorFees]=[SessionFees]*Rate from table rate/100

[AppFees]=[SessionFees]*Rate from table rate/100

need to finish this as soon as possible

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Carnation Flowers9y ago
Are there any code to help me
0
Carnation Flowers9y ago
need to triggers applu on all records
please help as soon as posiible
0
Nitin Sontakke9y ago
Under the circumstances, the trigger is the best solution.

For record, I hate triggers and must be avoided as far as possible.

If the version of your SQL Server supports, you should use INSTEAD OF trigger, which is best suited for your situation.

The trigger posted by Sekhar Babu assumes that only one record is getting inserted at a time, which will fail in case of bulk insert.
0
Sekhar Babu9y ago
Hello amani,

You can use triggers for auto calculation

use insert trigger like this
create trigger trg_ name on [dbo].[Sessions]
after insert
as
begin

declare @sessionid int,@sessionfees decimal,@mcfees decimal,@doctorfees decimal,@appfees decimal, @rate decimal

// first get the inserted details from inserted table

select @sessionid=sessionid,@sessionfees=sessionfees from inserted

// get rate from other table

select @rate=rate from ratetable [where condtion]

// now calculate other three rates with your required calculation similar to this

set @mcfees=@sessionfees*@rate
set @doctorfees =@sessionfees*@rate
set @appfees =@sessionfees*@rate
// now update calculated price back to table

update session set mcfees=@mcfees,doctorfees=@doctorfees,appfees=@appfees where sessionid=@sessionid

end
Know about triggers, they will be best suited for auto calculations insert, udpate,delete actions.

Hope it will help you.
0
Amit Gupta9y ago
Hey Amani

Yes, Definetely, this all can be done with computed column but as the data needs to be calculated from another table, you need to create a function with parameters and then use it in computed column of that particular column.

Reference for creating function
http://stackoverflow.com/questions/14550486/how-to-create-a-function-in-sql-server

Hope it helps
0
Carnation Flowers9y ago
But i want all operation happens in table auto
are there any way to do it auto
0
Ramesh Palanivel9y ago
Hi Amani,

Write a stroed procedure to insert the value in table,

Create the input parameter field in stored procedure, exmaple

@MCFees
@DoctorFees
@AppFees

perform your calculation and store the value appropriate fields and save the record
0