I have three tables:
Reference table:
CREATE TABLE [dbo].[ref_uharian]
(
[uhID] [int] IDENTITY(1,1) NOT NULL,
[uh_tahun] [int] NOT NULL,
[uh_kdlokasi] [int] NOT NULL,
[uh_nominal] [decimal](15, 2) NULL,
CONSTRAINT [PK_ref_uharian] PRIMARY KEY CLUSTERED
(
[uh_tahun] ASC,
[uh_kdlokasi] 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
Master table:
CREATE TABLE [dbo].[master_st]
(
[tugasID] [int] IDENTITY(1,1) NOT NULL,
[nost_generate] [varchar](20) NOT NULL,
[nost_asli] [varchar](20) NULL,
[nip] [varchar](18) NULL,
[kdperan] [int] NULL,
[gol] [varchar](3) NULL,
[eselon] [varchar](3) NULL,
[kdst] [int] NOT NULL,
[kdlokasi] [int] NOT NULL,
[tgl_mulai] [varchar](10) NOT NULL,
[tgl_selesai] [varchar](10) NOT NULL,
[jumlahhari1] [int] NOT NULL,
[jumlahhari2] [int] NULL,
CONSTRAINT [PK_master_st] PRIMARY KEY CLUSTERED
(
[tugasID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
TRANSACTION DETAIL table:
CREATE TABLE [dbo].[trs_uangharian]
(
[tr_id] [int] IDENTITY(1,1) NOT NULL,
[tugasID] [int] NOT NULL,
[uangharian20] [decimal](15, 2) NULL,
[uangharian80] [decimal](15, 2) NULL,
[uangharian100] [decimal](15, 2) NULL,
CONSTRAINT [PK_trs_uangharian] PRIMARY KEY CLUSTERED
(
[tr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
What I want to do is create a trigger so that when I insert a row into the MASTER table, it will insert new values into the TRANSACTION DETAIL based on table Reference.
uangharian100
uangharian20 (is 20% from uangharian 100)
uangharian80 (is 80% from uangharian 100)
to get the value of uangharian100 i have make a function (it has to do because [tgl_mulai] varchar datebegin and [tgl_mulai] varchar dateend is a varchar so I have to convert it to date first.
This is the Function
CREATE FUNCTION [dbo].[fc_col_uangharian100]
(
@tgl_mulai VARCHAR(10),
@tgl_selesai VARCHAR(10),
@kdlokasi INT
)
RETURNS DECIMAL
AS
BEGIN
DECLARE @date_diff INT, @thn_harian INT, @cst_uharian DECIMAL (15,2)
SET @thn_harian=CAST(YEAR(CONVERT(datetime, @tgl_mulai, 103)) AS INT)
SET @date_diff=((SELECT datediff(day,CONVERT([datetime],@tgl_mulai,(103)),CONVERT([datetime],@tgl_selesai,(103))))+1)
SET @cst_uharian=(SELECT uh_nominal FROM ref_uharian WHERE ((uh_tahun=@thn_harian) AND (uh_kdlokasi=@kdlokasi)))
RETURN ((@date_diff)*(@cst_uharian))
END
