BorlandTalk.com

Peet Koekemoer · Guest

Please help me I do not know a lot about triggers. I'm struggeling to create an insert trigger on a
MSSQL 2000 database. I need to Insert combined data from various tables into one table once a record was inserted into the main table. Now currently my trigger inserts all the data from the Main table into the new table when a new record is inserted, now I only want the new record that was inserted.

Main Table
Table Name PlanMonth
Key Fields ProdMonth, Sectionid, Workplaceid, Activity

New Table to where data needs to be inserted:
Table Name: BAAN_MonthlyData_Detail
Fields:
ID, (Key Field that will automatically be created)
ProdMonth,
FinYear,
FinMonth,
Department,
ProcessCode,
BACallArea,
WorkplaceID,
Production,
Processed,
ProcessedDate

This is my Trigger that does not work:

Create Trigger TR_BAAN_Plan_Insert
On PlanMonth
For Insert As
Insert into BAAN_MonthlyData_Detail
(BAAN_MonthlyData_Detail.ProdMonth, BAAN_MonthlyData_Detail.FinYear, BAAN_MonthlyData_Detail.FinMonth,
BAAN_MonthlyData_Detail.Department, BAAN_MonthlyData_Detail.ProcessCode, BAAN_MonthlyData_Detail.CallArea,
BAAN_MonthlyData_Detail.WorkplaceID, BAAN_MonthlyData_Detail.Production, BAAN_MonthlyData_Detail.Processed,
BAAN_MonthlyData_Detail.ProcessedDate)
SELECT
a.Prodmonth,
FinYear =
case
when Convert(Int,substring(convert(VarChar(6),a.ProdMonth),5,2)) > 6 then
Convert(Int,substring(convert(VarChar(6),a.ProdMonth),1,4)) + 1 else
Convert(Int,substring(convert(VarChar(6),a.ProdMonth),1,4))
end,
FinMonth =
case
when Convert(Int,substring(convert(VarChar(6),a.ProdMonth),5,2)) > 6 then
Convert(Int,substring(convert(VarChar(6),a.ProdMonth),5,2)) - 6 else
Convert(Int,substring(convert(VarChar(6),a.ProdMonth),5,2)) + 6
end,
Department = b.ReportToSectionid,
ProcessCode =
Case a.Activity
when 0 then 'STP' else 'DEV'
end,
c.CallArea,
a.Workplaceid,
Production =
Case a.Activity
when 0 then SqmTotal*-1 else Adv*-1
end,
Processed = 'N',
ProcessedDate = GetDate()
FROM
SECTION b
RIGHT OUTER JOIN PlanMonth a ON
b.Prodmonth = a.Prodmonth AND
b.SectionID = a.Sectionid
LEFT OUTER JOIN WORKPLACE d ON
a.Workplaceid = d.WorkplaceID
RIGHT OUTER JOIN EndType c ON
d.EndTypeID = c.EndTypeID
go

****************
I think somehow I need a where clause to identify the current inserted record but I do not know how to do this part.

Vitali Kalinin · Guest

Create Trigger TR_BAAN_Plan_Insert
On PlanMonth
For Insert As
Insert into BAAN_MonthlyData_Detail
(BAAN_MonthlyData_Detail.ProdMonth, BAAN_MonthlyData_Detail.FinYear,
BAAN_MonthlyData_Detail.FinMonth,
BAAN_MonthlyData_Detail.Department, BAAN_MonthlyData_Detail.ProcessCode,
BAAN_MonthlyData_Detail.CallArea,
BAAN_MonthlyData_Detail.WorkplaceID, BAAN_MonthlyData_Detail.Production,
BAAN_MonthlyData_Detail.Processed,
BAAN_MonthlyData_Detail.ProcessedDate)
SELECT
a.Prodmonth,
FinYear =
case
when Convert(Int,substring(convert(VarChar(6),a.ProdMonth),5,2)) > 6
then
Convert(Int,substring(convert(VarChar(6),a.ProdMonth),1,4)) + 1
else
Convert(Int,substring(convert(VarChar(6),a.ProdMonth),1,4))
end,
FinMonth =
case
when Convert(Int,substring(convert(VarChar(6),a.ProdMonth),5,2)) > 6
then
Convert(Int,substring(convert(VarChar(6),a.ProdMonth),5,2)) - 6
else
Convert(Int,substring(convert(VarChar(6),a.ProdMonth),5,2)) + 6
end,
Department = b.ReportToSectionid,
ProcessCode =
Case a.Activity
when 0 then 'STP' else 'DEV'
end,
c.CallArea,
a.Workplaceid,
Production =
Case a.Activity
when 0 then SqmTotal*-1 else Adv*-1
end,
Processed = 'N',
ProcessedDate = GetDate()
FROM
SECTION b
RIGHT OUTER JOIN inserted a ON --this is the trick you should use
inserted instead of PlanMonth
b.Prodmonth = a.Prodmonth AND
b.SectionID = a.Sectionid
LEFT OUTER JOIN WORKPLACE d ON
a.Workplaceid = d.WorkplaceID
RIGHT OUTER JOIN EndType c ON
d.EndTypeID = c.EndTypeID