omerix's questions

我的数据库中有自己的触发器。但我的客户想要定制。

我需要根据他想要的优先顺序给出触发顺序。

我知道我可以用 1 个触发器完成所有这些操作，如果是背靠背的话。

但我想 custom_xCompanyTrigger 我的客户想要的触发器，并按照我想要的顺序放置。

如果有多个触发器，它要么按创建顺序工作，要么按名称顺序工作，我无法理解。

这样，我应该如何进行以客户为基础的定制？

CREATE TABLE [dbo].[MyTable](
    [name] [varchar](50) NULL,
    [city] [varchar](50) NULL,
    [date] [date] NULL,
    [value] [int] NULL,
    [islocked] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'John', N'Berlin', CAST(N'2022-01-02' AS Date), 10,1)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Marty', N'Istanbul', CAST(N'2022-01-03' AS Date), 20, 1)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Aila', N'Washington', CAST(N'2022-01-05' AS Date), 30, 1)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Aise', N'Moskova', CAST(N'2022-01-01' AS Date), 40, 1)

4 rows affected

create TRIGGER MyTable_Trigger_Delete2 ON dbo.MyTable
AFTER DELETE AS

BEGIN

  /* Control-2 */
    IF ((SELECT COUNT(*) FROM DELETED WHERE date<='2022-01-04')>0)

        BEGIN
            PRINT 'Control-2 worked.'
            RAISERROR ('You cannot delete records before January 5th.', 16, 1)
            ROLLBACK TRANSACTION
        END
     else
           PRINT 'Control-2 passed.'


END

create TRIGGER MyTable_Trigger_Delete1 ON dbo.MyTable
AFTER DELETE AS

BEGIN

  /* Control-1 */
    IF ((SELECT COUNT(*) FROM DELETED WHERE islocked=1)>0)

        BEGIN
            PRINT 'Control-1 worked.'
            RAISERROR ('You cannot delete a locked recording.', 16, 1)
            ROLLBACK TRANSACTION
        END
     else
           PRINT 'Control-1 passed.'

END

select * from MyTable

delete from MyTable where city='Berlin'

Msg 50000 Level 16 State 1 Line 11
You cannot delete records before January 5th.
Msg 3609 Level 16 State 1 Line 1
The transaction ended in the trigger. The batch has been aborted.

select * from MyTable

小提琴

我有一张桌子。我的表中有一个字段显示“islocked”记录已锁定。此字段数可以超过一个。isApproved, isAccepted 等

有各种触发器（删除后等）。触发器内部运行着程序。

程序内部，有逐条删除的操作，也有批量删除的操作。

如果记录被锁定，用户要求我防止删除。

我不想在后触发器中编码，而是想用它之前的“INSTEAD OF DELETE”触发器来完成。

但是它说记录已删除。它没有被删除。

永远不会删除多个记录。

如果批量删除中有锁定和解锁的记录，也不起作用。

我哪里做错了？

同时，如果这个触发器出错，我希望其他触发器根本不起作用。可以优先考虑吗？

CREATE TABLE [dbo].[MyTable](
    [name] [varchar](50) NULL,
    [city] [varchar](50) NULL,
    [date] [date] NULL,
    [value] [int] NULL,
    [islocked] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'John', N'Berlin', CAST(N'2022-01-02' AS Date), 10, 0)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Marty', N'Istanbul', CAST(N'2022-01-03' AS Date), 20, 1)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Aila', N'Washington', CAST(N'2022-01-05' AS Date), 30, 0)
INSERT [dbo].[MyTable] ([name], [city], [date], [value], [islocked]) VALUES (N'Aise', N'Moskova', CAST(N'2022-01-01' AS Date), 40, 1)

create TRIGGER MyTable_Trigger_Delete ON dbo.MyTable
INSTEAD OF DELETE AS

BEGIN

    IF ((SELECT COUNT(*) FROM DELETED WHERE islocked=1)>0)

        BEGIN
            RAISERROR ('You cannot delete a locked recording.', 16, 1)
            ROLLBACK TRANSACTION
        END

END

select * from MyTable

select * from MyTable where (city='Istanbul' OR city='Berlin')

delete from MyTable where (city='Istanbul' OR city='Berlin')

Msg 50000 Level 16 State 1 Line 9
You cannot delete a locked recording.
Msg 3609 Level 16 State 1 Line 1
The transaction ended in the trigger. The batch has been aborted.

select * from MyTable

delete from MyTable where city='Berlin'

1 rows affected

select * from MyTable

小提琴

我有 2 个表“订单”和“销售”。productCode 字段在该表中很常见。

我的目标是按如下方式并排“加入”2 个表。

我试过的

1. partition by (row_number() over (partition by productCode order by id))
2. 分组集
3. 全外连接
4. ...当前行和 1 个后续行..

CREATE TABLE [dbo].[orders](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [orderDate] [date] NULL,
    [productCode] [varchar](50) NULL,
    [qty] [float] NULL,
    [price] [float] NULL,
    [deadLine] [date] NULL,
 CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[sales](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [salesDate] [date] NULL,
    [productCode] [varchar](50) NULL,
    [qty] [float] NULL,
    [price] [float] NULL,
 CONSTRAINT [PK_sales] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[orders] ON 
INSERT [dbo].[orders] ([id], [orderDate], [productCode], [qty], [price], [deadLine]) VALUES (1, CAST(N'2022-10-01' AS Date), N'a', 10, 15, CAST(N'2022-12-01' AS Date))
INSERT [dbo].[orders] ([id], [orderDate], [productCode], [qty], [price], [deadLine]) VALUES (2, CAST(N'2022-10-02' AS Date), N'b', 20, 25, CAST(N'2023-01-01' AS Date))
INSERT [dbo].[orders] ([id], [orderDate], [productCode], [qty], [price], [deadLine]) VALUES (3, CAST(N'2022-10-03' AS Date), N'c', 30, 35, CAST(N'2023-02-02' AS Date))
INSERT [dbo].[orders] ([id], [orderDate], [productCode], [qty], [price], [deadLine]) VALUES (4, CAST(N'2022-10-04' AS Date), N'd', 40, 45, CAST(N'2023-03-03' AS Date))
SET IDENTITY_INSERT [dbo].[orders] OFF


SET IDENTITY_INSERT [dbo].[sales] ON 
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (1, CAST(N'2022-11-01' AS Date), N'a', 3, 14)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (2, CAST(N'2022-11-02' AS Date), N'b', 4, 25)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (3, CAST(N'2022-11-03' AS Date), N'a', 1, 16)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (4, CAST(N'2022-11-04' AS Date), N'b', 3, 20)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (5, CAST(N'2022-11-05' AS Date), N'b', 8, 24)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (6, CAST(N'2022-11-06' AS Date), N'a', 5, 24)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (7, CAST(N'2022-11-07' AS Date), N'b', 41, 20)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (8, CAST(N'2022-11-08' AS Date), N'd', 10, 35)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (9, CAST(N'2022-11-09' AS Date), N'a', 4, 10)
SET IDENTITY_INSERT [dbo].[sales] OFF

数据库小提琴

我正在“材料”表中创建一个“bom”列表条目。

我将为产品组定义的材料带到网格中，以便用户不会忘记相关材料。

用户通过此模板定义。

但如果他们愿意，他们会添加额外的材料（组模板除外）。

如果第二条select语句中有记录；我不想在 1 条选择语句中显示记录。

我从 lvl 列中了解到，该记录应该在第二个 select 子句中被粉碎。（已对编号为 2 的模板进行了定义）

CREATE TABLE [dbo].[materials](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [productCode] [varchar](50) NULL,
    [description] [varchar](250) NULL,
    [usageQauntity] [float] NULL,
    [process] [varchar](50) NULL,
    [vendor] [varchar](50) NULL,
    [lvl] [int] NULL,
 CONSTRAINT [PK_materials] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[product](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [code] [varchar](50) NULL,
    [name] [varchar](250) NULL,
    [groupCode] [varchar](50) NULL,
    [unit] [varchar](10) NULL,
    [price] [float] NULL,
    [minStock] [int] NULL,
    [maxStock] [int] NULL,
 CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[materials] ON 
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (1, N'pencil', N'paint chemical', 0.15, N'1', N'ABC', 1)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (2, N'pencil', N'pen inside', 1, N'1', N'DEF', 2)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (4, N'pencil', N'cover', 1, N'2', N'GHD', 3)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (5, N'pencil', N'brand label', 1, N'0', N'ABC', 4)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (6, N'lp', N'top eraser', 1, N'0', N'', 0)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (7, N'lp', N'DEF brand pencil inside', 1, N'1', N'DEF', 2)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (8, N'eraser', N'chemical', 1, N'0', N'ABC', 4)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (9, N'eraser', N'brand label', 1, N'0', N'ABC', 4)
SET IDENTITY_INSERT [dbo].[materials] OFF

SET IDENTITY_INSERT [dbo].[product] ON 
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (1, N'lp', N'lead pencil', N'pencil', N'piece', 15, 1000, 2000)
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (2, N'bp', N'ball-point pen', N'pencil', N'piece', 25, 2000, 3000)
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (3, N'sp', N'stylographic pen', N'pencil', N'piece', 50, 1000, 2000)
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (4, N'me', N'Magnetic eraser', N'eraser', N'piece', 30, 1000, 1500)
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (5, N'se', N'Soft Eraser', N'eraser', N'piece', 20, 1000, 2000)
SET IDENTITY_INSERT [dbo].[product] OFF

13 rows affected

select * from product
select * from materials

select p.id,p.code,m2.*
from product p
left join materials m2 on m2.productCode=p.groupCode
where p.code='lp'

select  p.id,p.code,m1.*
from product p
left join materials m1 on m1.productCode=p.code
where p.code='lp'

--test: Since lvl=2 is defined in the product, materials.id=2 should not appear.
select  p.id,p.code,m1.*
from product p
left join materials m1 on (m1.productCode=p.code or m1.productCode=p.groupCode)
where p.code='lp'

小提琴

我有一个如下的程序语句。我的更新语句根据此处的@role 参数而变化。

是否可以根据“rolePrivileges”表在程序中动态造句？

CREATE TABLE [dbo].[myTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [dataField1] [varchar](10) NULL,
    [dataField2] [varchar](10) NULL,
    [dataField3] [varchar](10) NULL,
    [dataField4] [varchar](10) NULL,
    [userId] [int] NULL,
    [recTime] [datetime] NULL,
    [recUniq] [uniqueidentifier] NULL,
 CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[myTable] ADD  CONSTRAINT [DF_myTable_recTime]  DEFAULT (getdate()) FOR [recTime]
ALTER TABLE [dbo].[myTable] ADD  CONSTRAINT [DF_myTable_recUniq]  DEFAULT (newid()) FOR [recUniq]

create PROCEDURE myTableProcedure 
     @id int=0
    ,@dataField1 varchar(10) = '' 
    ,@dataField2 varchar(10) = ''
    ,@dataField3 varchar(10) = ''
    ,@dataField4 varchar(10) = ''
    ,@userId int=0
    ,@role tinyint=0 --1=All Column, 2=dataField1,dataField2, 3=dataField1,dataField3,dataField4 (not=2)
AS
BEGIN
    SET NOCOUNT ON

    --New Record
    if (@id=0)
    Begin
    insert into myTable (dataField1,dataField2,dataField3,dataField4,userId) values (@dataField1,@dataField2,@dataField3,@dataField4,@userId)
    End 
    --Updated
    else
    Begin
---------------------------- Variable partition based on @role value.
        if (@role=1)
        begin
            update myTable set
                 dataField1=@dataField1
                ,dataField2=@dataField2
                ,dataField3=@dataField3
                ,dataField4=@dataField4
                ,recTime=getdate()
            where id=@id
        end
        else if (@role=2)
        begin
            update myTable set
                 dataField1=@dataField1
                ,dataField2=@dataField2
                --,dataField3=@dataField3
                --,dataField4=@dataField4
                ,recTime=getdate()
            where id=@id
        end
        else if (@role=3)
        begin
            update myTable set
                 dataField1=@dataField1
                --,dataField2=@dataField2
                ,dataField3=@dataField3
                ,dataField4=@dataField4
                ,recTime=getdate()
            where id=@id
        end
---------------------------- Variable partition based on @role value./
    End
END

--role=1 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=789
    ,@role=1
--role=2 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=123
    ,@role=2
--role=3 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=456
    ,@role=3

select * from myTable

--role=1 update
execute myTableProcedure 
     @id=1
    ,@dataField1= '11' 
    ,@dataField2= '11'
    ,@dataField3= '11'
    ,@dataField4= '11'
    ,@userId=789
    ,@role=1
--role=2 update
execute myTableProcedure 
     @id=2
    ,@dataField1= '22' 
    ,@dataField2= '22'
    ,@dataField3= '22'
    ,@dataField4= '22'
    ,@userId=123
    ,@role=2
--role=3 update
execute myTableProcedure 
     @id=3
    ,@dataField1= '33' 
    ,@dataField2= '33'
    ,@dataField3= '33'
    ,@dataField4= '33'
    ,@userId=456
    ,@role=3

select * from myTable

小提琴

我将sql中的别名值放入一个字符串语句中，并不断生成一个带有replace的新字符串。

但是我必须做很多以这种方式嵌套的“替换”。

string 子句中的 {{name}} 实际上是 sql 子句中的别名

有没有办法定义 sql 和模板子句并使其自动化？

样本：

CREATE TABLE [dbo].[message](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [quantity] [float] NULL,
    [delivery] [datetime] NULL,
    [status] [tinyint] NULL,
)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Omer', 124, CAST(N'2022-10-18T00:00:00.000' AS DateTime), 0)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Jacob', 548, CAST(N'2022-11-05T00:00:00.000' AS DateTime), 0)
INSERT [dbo].[message] ([name], [quantity], [delivery], [status]) VALUES ( N'Hasan', 56454, CAST(N'2022-08-09T00:00:00.000' AS DateTime), 1)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Hans', 548, CAST(N'2023-11-01T00:00:00.000' AS DateTime), 0)

4 rows affected

select * from message where status=0

declare @template nvarchar(max)='Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.'
print @template

select 
replace(
replace(
replace(@template
,'{{name}}',name)
,'{{quantity}}',quantity)
,'{{delivery}}',format(delivery,'dd.MM.yyy'))
from message where status=0

Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.

declare @template nvarchar(max)='Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.'
print @template

declare @sqlString nvarchar(max)='select name,quantity,format(delivery,''dd.MM.yyy'') as [delivery] from message where status=0'
print @sqlString

Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.
select name,quantity,format(delivery,'dd.MM.yyy') as [delivery] from message where status=0

/*
template embed code

*/

小提琴

我有 2 个表格 DraftProducts 和 Products。

两个表之间的唯一区别是“阶段”字段。

我的 DraftProducts 表中有 productCode varchar(20)、phase(tinyint)、Name、Price、SpecialCells.... 等字段。

对我来说，最后一个阶段实际上是 Product 表中的 1 条记录。

直到现在，当 1 位用户说草稿工作完成时，我自动在 Product 表中记录了最后一个阶段。

但是当最后的草稿发生变化时，它需要再次更新。删除时需要删除它等。很多额外的工作正在进行中。

在我的数据库中，一些表来自 DraftProducts 表，一些表来自 Products 表。

但是我可以将其作为独立于用户的“产品视图”来执行吗？

如何使用 max(phase) 1 记录创建“ProductsView”？我应该如何分组或区分？

由于这个表，我想要的 Products 表或视图应该是这样的。

p1=8 价格、类别、描述等 p2=15 价格、类别、描述等 p3=22 价格、类别、描述等

CREATE TABLE [dbo].[DraftProducts](
    [productCode] [varchar](20) NULL,
    [phase] [varchar](50) NULL,
    [name] [varchar](50) NULL,
    [category] [varchar](20) NULL,
    [description] [varchar](20) NULL,
    [price] float NULL
)

CREATE UNIQUE CLUSTERED INDEX [DraftProductsIndex1] ON [dbo].[DraftProducts]
(
    [productCode] ASC,
    [phase] ASC
)

insert into DraftProducts (productCode,phase,name)
values 
('p1',1,'aaaaaaaaaaaaaaa'),
('p2',2,'aaaaaaaaaaaaaaa'),
('p3',1,'aaaaaaaaaaaaaaa'),
('p2',15,'bbbbbbbbbbbbbbb'),
('p3',22,'bbbbbbbbbbbbbbb'),
('p1',8,'bbbbbbbbbbbbbbbbbbbbbbbbb'),
('p2',7,'ccccccccccccccc')

7 rows affected

select * from DraftProducts 

/*
finish phase
p1=8
p2=15
p3=22
*/

小提琴