我在MSSQL（2012）DB（“ tblA”和“ tblB”）上有2个表格。在“ tblA”上插入后，我希望触发器在“ tblB”上自动生成日期从“ tblA”到STARTDATE到ENDDATE之间的记录，但仅在“ tblA”上选择的工作日
例如在“ tblA”上：
tblA_ID: 99
CAT: TEXT1
STARTDATE: 01/01/2015
ENDDATE: 31/01/2015
MONDAY: true
TUESDAY: false
WEDNESDAY: false
THURSDAY: false
FRIDAY: true
SATURDAY: false
SUNDAY: false
“ tblB”应该得到
1 99 TEXT1 02/01/2015 (=Friday)
2 99 TEXT1 04/01/2015 (=Monday)
3 99 TEXT1 09/01/2015 (=Friday)
4 99 TEXT1 11/01/2015 (=Monday)
5 99 TEXT1 16/01/2015 (=Friday)
6 99 TEXT1 28/01/2015 (=Monday)
7 99 TEXT1 23/01/2015 (=Friday)
8 99 TEXT1 25/01/2015 (=Monday)
9 99 TEXT1 30/01/2015 (=Friday)
CREATE TABLE [dbo].[tblA]
(
[tblA_ID] INT NOT NULL IDENTITY,
[tblA_CAT] NVARCHAR(50) NULL,
[tblA_STARTDATE] DATE NULL,
[tblA_ENDDATE] DATE NULL,
[tblA_MONDAY] BIT NULL,
[tblA_TUESDAY] BIT NULL,
[tblA_WEDNESDAY] BIT NULL,
[tblA_THURSDAY] BIT NULL,
[tblA_FRIDAY] BIT NULL,
[tblA_SATURDAY] BIT NULL,
[tblA_SUNDAY] BIT NULL,
CONSTRAINT [PK_tblA] PRIMARY KEY ([tblA_ID]),
)
CREATE TABLE [dbo].[tblB]
(
[tblB_ID] INT NOT NULL IDENTITY,
[tblA_ID] INT NOT NULL,
[tblB_CAT] NVARCHAR(50) NULL,
[tblB_DATE] DATE NULL,
CONSTRAINT [PK_tblB] PRIMARY KEY ([tblB_ID]),
)
此触发器应为您工作。它使用递归公用表表达式生成tblB_DATE该范围内的所有值，然后使用case表达式测试日期是否在标记为true的那些值中。请注意，这部分取决于系统语言，因此，如果您不使用英语，则必须根据您的特定语言调整日期名称文字。
递归公用表表达式可以代替日历表，因此，如果您有一个合适的表可以用作日期源，则可以改用它（它应该提供更好的性能）。
还请注意，该代码缺少错误检查且尚未进行优化，因此可能需要改进。它应该使您对如何解决问题有所了解。
CREATE TRIGGER ins_tblb ON [dbo].[tblA] AFTER INSERT AS
BEGIN
WITH dates AS (
SELECT
tblA_ID, tbla_CAT, tbla_STARTDATE, tbla_ENDDATE,
tbla_MONDAY, tbla_TUESDAY, tbla_WEDNESDAY, tbla_THURSDAY,
tbla_FRIDAY, tbla_SATURDAY, tbla_SUNDAY,
tblA_STARTDATE tblB_DATE
FROM inserted
UNION ALL
SELECT tblA_ID, tbla_CAT, tbla_STARTDATE, tbla_ENDDATE,
tbla_MONDAY, tbla_TUESDAY, tbla_WEDNESDAY, tbla_THURSDAY,
tbla_FRIDAY, tbla_SATURDAY, tbla_SUNDAY,
DATEADD(DAY, 1, tblB_DATE) tblB_DATE
FROM dates
WHERE dates.tblB_DATE <= tblA_ENDDATE
)
INSERT tblB (tblA_ID, tblB_CAT, tblB_DATE)
SELECT tblA_ID, tblA_CAT, tblB_DATE
FROM dates
WHERE DATENAME(DW,tblB_DATE) IN (
CASE WHEN tblA_MONDAY = 1
THEN N'Monday' END,
CASE WHEN tblA_TUESDAY = 1
THEN N'Tuesday' END,
CASE WHEN tblA_WEDNESDAY = 1
THEN N'Wednesday' END,
CASE WHEN tblA_THURSDAY = 1
THEN N'Thursday' END,
CASE WHEN tblA_FRIDAY = 1
THEN N'Friday' END,
CASE WHEN tblA_SATURDAY = 1
THEN N'Saturday' END,
CASE WHEN tblA_SUNDAY = 1
THEN N'Sunday' END
)
ORDER BY tblA_ID, tblB_DATE
OPTION (MAXRECURSION 0)
END
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句