need help as soon as possible

Question

i create database and all relations i have Session table USE [OnCall] GO /****** Object: Table [dbo].[Sessions] Script Date: 03/14/2017 21:08:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO 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] GO ALTER TABLE [dbo].[Sessions] ADD CONSTRAINT [DF_Sessions_NumOfVisit] DEFAULT ((1)) FOR [NumOfVisit] GO ------------------ 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

Amit Gupta · Answer

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

Carnation Flowers · Answer

Are there any code to help me

Carnation Flowers · Answer

need to triggers applu on all records please help as soon as posiible

Nitin Sontakke · Answer

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.

Sekhar Babu · Answer

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=@mcfeesdoctorfees=@doctorfeesappfees=@appfees where sessionid=@sessionid end Know about triggers they will be best suited for auto calculations insert udpatedelete actions. Hope it will help you.

Carnation Flowers · Answer

But i want all operation happens in table auto are there any way to do it auto

Ramesh Palanivel · Answer

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