问题[trigger](dba)

场景：我拥有一个 ETL 流程。该流程涉及从不同来源提取数据并使我的数据库与最新数据同步。

假设我有一个名为 [catalog] 的表。它包含我公司的产品目录。此表的可信来源每四个小时向我发送一个 CSV。我提取该 CSV 并确保我的 [catalog] 表与其同步。我使用如下更新：

UPDATE mc 
SET
      [field1] = tc.[field1]
    , [field2] = tc.[field2]
FROM [my].[catalog] as mc  
INNER JOIN [their].[catalog] as tc
    ON mc.id = tc.id
WHERE mc.[field1] <> tc.[field1]
OR mc.[field2] <> tc.[field2]

此命令将比较每个匹配项，并仅筛选出那些将看到有意义更改的行，然后仅更新这些行。我不想不必要地触碰任何行——这是 ETL 开发人员的长期任务。

不会执行任何琐碎更新（所谓琐碎更新，我指的是对某一行的更新，但该行中的任何字段均未发生改变）。未发生改变的行将保持原样，不受干扰。

这里用“不受干扰”这个词很贴切，因为更新一行而不影响值会产生不必要的成本。在内存和磁盘中，对行的更新会从物理上删除包含旧值的行，并插入包含新值的新行。所有这些更新都必须记录！因此，在什么都不做的过程中，我们已经在内存和磁盘上对表的索引和数据页进行了碎片化和分割。而且我们正在通过日志进行不必要的流量。

如果我们不做任何更改，那么简单的更新仍会导致内存和磁盘的混乱。为表提供一种自动忽略简单更新的方法，对于许多场景来说都是一大优势。

现在，假设某个笨手笨脚的开发人员通过某个客户端或程序更改进行了更新，该更新对大型表执行了类似操作，但没有包括任何措施来忽略“真正”不需要更新的行。我可以对表执行什么操作，让它表现得好像知道传递琐碎的更新一样？

IBM DB2 on Cloud，版本 11.5.9.0

尝试在表上创建触发器（为了简化，对象名称是虚构的）：

CREATE OR REPLACE TRIGGER MY_TRIGGER
AFTER UPDATE OR INSERT ON MY_TABLE
REFERENCING NEW TABLE AS NEW_STUFF
FOR EACH STATEMENT
BEGIN ATOMIC
    DELETE FROM TABLE_A WHERE P_N IN (SELECT DISTINCT P_N FROM NEW_STUFF);
    INSERT INTO TABLE_A SELECT * FROM VIEW_B WHERE P_N IN (SELECT DISTINCT P_N FROM NEW_STUFF);
END;

视图 VIEW_B 在其定义中确实有 TABLE_A 作为引用。

我收到此错误：

SQL Error [42601]: An unexpected token "OR INSERT" was found following "".  
Expected tokens may include:  "".. 
SQLCODE=-104, SQLSTATE=42601

如果我创建仅使用AFTER UPDATE ON MY_TABLE或仅使用AFTER INSERT ON MY_TABLE 的相同触发器，它会正常工作。当我同时指定AFTER UPDATE 或 INSERT时，它会失败。

如果我尝试BEGIN而不是BEGIN ATOMIC我会得到不同的错误：

SQL Error [42898]: The definition of trigger "MY_SCHEMA.MY_TRIGGER" includes an 
invalid use of correlation name or transition table name "NEW_STUFF".
Reason code="4".. SQLCODE=-696, SQLSTATE=42898

我研究了文档和我能找到的几个例子。如果有人知道为什么 UPDATE 或 INSERT 的组合会导致它失败，我将不胜感激。

我正在运行以下选择：

SELECT tstats.object_id, tstats.database_id, tstats.sql_handle, 
DB_NAME(tstats.database_id) AS [db_name], OBJECT_NAME(tstats.object_id) AS      
trigger_name, tstats.execution_count, tstats.total_worker_time,     
tstats.total_logical_reads, tstats.total_logical_writes, OBJECT_NAME(trgs.parent_id) AS  
trigger_parent, getdate() AS [timestamp] FROM sys.dm_exec_trigger_stats tstats INNER 
JOIN  sys.triggers trgs ON tstats.object_id = trgs.object_id ORDER BY object_id asc

SQL Server 版本是 2019 (RTM-CU24) 标准。

问题是，某些触发器返回两次（相同的对象 ID、不同的执行计数和其他统计信息）：

我正在尝试对这个sys.dm_exec_trigger_statsDMV 进行采样并每分钟生成增量，以查看哪些触发器正在影响应用程序性能 - 我认为数据库名称、触发器的父名称和触发器名称（或 object_id）的组合是唯一的。它们多次返回，具有不同的运行时统计信息，我无法理解。提示表示赞赏。

基本上，复合主键错误“UNIQUE约束失败”的下一步是：我在插入后创建了一个触发器，如果​​它小于一，则将其设置SEQ为最高加一。SEQ基本上：

CREATE TRIGGER TRG_NAMES_ADD2 AFTER INSERT ON NAMES FOR EACH ROW
WHEN NEW.SEQ < 1
BEGIN
    UPDATE NAMES SET
        SEQ = (SELECT IFNULL(MAX(SEQ), 0) + 1 FROM NAMES WHERE TYPE = NEW.TYPE)
    WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
END;

不过，我想TYPE独立于做同样的事情SEQ。我发现的问题是每个触发器只能有一个UPDATE，并且AFTER INSERT触发器可以按任何顺序执行。

我尝试了第二个触发器，如下所示：

CREATE TRIGGER TRG_NAMES_ADD1 AFTER INSERT ON NAMES FOR EACH ROW
WHEN NEW.TYPE < 1
BEGIN
    UPDATE NAMES SET
        TYPE = (SELECT IFNULL(MAX(TYPE), 0) + 1 FROM NAMES),
        SEQ = 1
    WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
END;

但是为了完整性，TRG_NAMES_ADD2应该在 后运行TRG_NAMES_ADD1，否则我必须将两个操作（设置TYPE为可能的新值（如果< 1），然后使用该新值也可能为SEQ（如果< 1）设置新值）放入一个触发器。

不幸的是，这超出了我的能力范围。

为了方便起见，这里是表创建和一些示例插入：

CREATE TABLE NAMES (
TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20),
PRIMARY KEY (TYPE, SEQ)
);

insert into NAMES (type, seq, name) values (2, 3, "T1");
insert into NAMES (type, seq, name) values (2, 0, "T2");
insert into NAMES (type, seq, name) values (2, 0, "T3");
insert into NAMES (type, seq, name) values (0, 1, "T4");
insert into NAMES (type, seq, name) values (0, 0, "T5");

所以任务应该是这样的：

T|S|N
-----
2|3|T1  # T,S set manually
2|4|T2  # S set automatically
2|5|T3  # S set automatically
3|1|T4  # T set automatically
4|1|T5  # T,S set automatically

通过小 SQL(ite) 实践，我尝试创建一个具有复合主键的表：

CREATE TABLE NAMES (
  TYPE INTEGER NOT NULL,
  SEQ INTEGER NOT NULL,
  NAME VARCHAR(20),
  PRIMARY KEY (TYPE, SEQ)
);

这个想法是存储从数字到名称的关联，即：SEQ往返NAME。由于此类“类”的数量可变，因此我添加了一TYPE列，即SEQ数字（和NAMEs）在特定的TYPE.

我还尝试创建一个触发器，该触发器将自动分配SEQ特定中的“下一个空闲”号码，TYPE如下所示：

CREATE TRIGGER TRG_NAMES_ADD AFTER INSERT ON NAMES
FOR EACH ROW BEGIN
    UPDATE NAMES SET SEQ = (SELECT COUNT(1) FROM NAMES WHERE TYPE = NEW.TYPE)
    WHERE TYPE = NEW.TYPE;
END;

当我插入第一个条目时，它似乎有效，但是当我尝试添加第二个条目时，它失败了

运行时错误：UNIQUE 约束失败：NAMES.TYPE、NAMES.SEQ (19)

像这样：

sqlite> select * from NAMES;
sqlite> insert into NAMES (type, seq, name) values (0, -1, "test");             sqlite> select * from NAMES;
0|1|test
sqlite> insert into NAMES (type, seq, name) values (0, -1, "test2");
Runtime error: UNIQUE constraint failed: NAMES.TYPE, NAMES.SEQ (19)
sqlite> insert into NAMES (type, seq, name) values (1, -1, "test2");
sqlite> select * from NAMES;
0|1|test
1|1|test2

所以触发器似乎对每个TYPE都只工作一次，但也许不起作用，因为第一个SEQ应该是 0 而不是 1。

我也尝试过使用BEFORE而不是AFTER触发器，但这不会改变SEQ一切（使用-1）。

触发器有什么问题，还是表定义有问题？

我有一个包含 75 列的表，并且需要跟踪其中大约 15 列，以便在这 15 列中的任何列中的任何数据发生变化（仅更新，无插入或删除）时注销旧值和新值。例外情况是我们只想跟踪这 15 个列中更新的列。如果旧值和新值均为 NULL 或两者相同，我们只想将 NULL 记录到这些列的审计表中。

多列可以为空，并且是整数、小数和 nvarchar 数据类型的组合。

这一切都在 Azure SQL 数据库上运行。

我写了一个初始的更新后触发器，它有一个插入语句到一个有 30 列的审计表中，一个“旧”和“新”列用于我们希望跟踪的所有 15 个列。当旧值和新值都不为 NULL 时，这一切都很好，但如果旧值或新值之一恰好为 NULL，我们就会丢失数据。

为了解决这个问题，我开始为每一列编写 case 语句，以及一堆 where 子句，但我觉得这不是正确的方法。基本上，有 15 种不同的语句变体，例如：

CREATE OR ALTER TRIGGER [dbo].[trg_Stuff_Audit]
ON [dbo].[Stuff]
AFTER UPDATE
AS
BEGIN

insert into dbo.audit (id, OldDecimalValue, NewDecimalValue)

select i.id,

case when d.DecimalValue is null and i.DecimalValue is not null then d.DecimalValue
when d.DecimalValue is not null and i.DecimalValue is null then d.DecimalValue
when d.DecimalValue <> i.DecimalValue then d.DecimalValue
else NULL end as OldDecimalValue,

case when d.DecimalValue is null and i.DecimalValue is not null then i.DecimalValue
when d.DecimalValue is not null and i.DecimalValue is null then i.DecimalValue
when d.DecimalValue <> i.DecimalValue then i.DecimalValue
else NULL end as NewDecimalValue

from inserted i inner join deleted d on i.id = d.id

where d.DecimalValue is null and i.DecimalValue is not null
OR d.DecimalValue is not null and i.DecimalValue is null
OR d.DecimalValue <> i.DecimalValue

END;

我觉得必须有更好的方法来解决这个问题，我是走在正确的道路上还是需要改变方向？

我制作了下表：

CREATE table IF NOT EXISTS redirect (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            url_from TEXT not null,
            url_to TEXT not null,
            method TEXT not null,
            http_status_code INTEGER not null CHECK( http_status_code IN (300,301,302,304,304,308,307) ) DEFAULT 301,
            use_in_https INTEGER not null CHECK(use_in_https IN (0,1)) DEFAULT 0,
            use_in_http INTEGER not null CHECK(use_in_http IN (0,1)) DEFAULT 1,
            exact_match INTEGER not null CHECK(exact_match IN (0,1)) DEFAULT 1
        );

我做了以下触发器：

        CREATE TRIGGER remove_http_https BEFORE INSERT ON redirect BEGIN 
           SELECT CASE WHEN NEW.url_from THEN REPLACE(REPLACE(NEW.url_from,"http:\/\/",""),"https:\/\/","")   END;
        END;

但是我希望触发器在我更新值或插入值时同时运行。有没有办法将插入和更新定义为 sqlite3 的单个触发器？

所以我有这三张桌子。订单表是现有订单的列表，而订单详细信息由订单中的项目组成（因为 1 个订单可以有多个目录）。我正在使用 mysql

客户订单

订单项

存货

我想创建一个触发器，如果​​订单表中的 shipping_status 为“已发货”，那么它将更新库存中的库存。

我已经创建了这个触发器

create trigger update_inventory
after update on cust_order
for each row 
begin
if new.shipping_status="Shipped" then
update inventory 
set inStock = inStock - (select quantity from order_item where order_id=new.order_id)
where catalog_id in (select catalog_id from order_item where order_id=new.order_id);
end if;
end;
//

但它不起作用并转为 #1242 - 子查询返回超过 1 行

如何创建一次更新多行的触发器？

我有许多用 PL/pgSQL 编写的触发函数，它们遵循以下逻辑：

SELECT my_name
INTO l_my_name
FROM data
WHERE data.my_number = NEW.test_num;

IF NOT FOUND THEN
   INSERT INTO test_event
   VALUES (NEW.test_num, NULL, NEW.event_one, NEW.event_two);
ELSE                                                                                            
   INSERT INTO test_event
   VALUES (NEW.test_num, l_my_name, NEW.event_one, NEW.event_two);
END IF;

一些插入具有超过 15 个以上的值，我试图将它们简化为仅 1 个插入，因为上面的代码一遍又一遍地重复存储过程中的不同检查，不必要地使过程有数百行代码长。我的想法是这样的：

INSERT INTO test_event
VALUES (NEW.test_num, NULLIF(l_my_name,''), NEW.event_one, NEW.event_two);

我不确定如果没有找到它是否l_my_name会正常工作，或者我是否必须做一些不同的事情。''NULLIF

如果某些特定数据在另一个表中可用，我需要一个可以防止更新特定列数据的 MySQL 触发器。

我有两张表产品和销售。

产品

销售

如果 product_id 在销售表中可用，我需要防止更新产品表中的 updated_at 列。