SQL帮助中的触发器 [英] Triggers in SQL Help

问题描述

我正在寻找一些有关创建触发器的帮助.以下是我更正的第一个触发器.如果您有正确的想法或一些有用的提示，请告诉我.

I''m looking for a little help in creating triggers. Below is the first trigger that I have corrected. Please could you let me know if I have the right Idea or maybe some helpful hints.

Create Table CustomerLog (
	CustomerLogID int IDENTITY(1,1),
	CustomerID int NOT NULL,
	Email varchar(100),
	LogDate datetime
	)
GO

Create TRIGGER trgCustomerInsert 
ON Customer 
FOR INSERT AS

	DECLARE @TheCustomerID int
	DECLARE @Email varchar (100)
	Select @TheCustomerID = CustomerID From inserted
	Select @Email = Email From inserted
	INSERT INTO CustomerLog (CustomerLogID, CustomerID, Email, LogDate)
		Values('CustomerLogID',@TheCustomerID,@Email,GetDate())

GO

我必须创建的下一个触发器是INSERT UPDATE触发器，在这里我将更新其他表上的QTY.我相信我大部分都是正确的，但是我不确定如何为产品表编写更新.触发器设计中我做错了什么?我真的很困惑如何获得正确的输出.是否有一些信息可以帮助我解决问题.

The next trigger I have to create is a INSERT UPDATE trigger where I''m updating the QTY on a different table. I believe I have the most of it correct however I''m not sure on how to write the update for the product table. What am I doing wrong in the trigger design? I''m really confused on how to get the correct output. Is there some information that would shine some light on my problem.

Create Table CustomerOrderItemLog (
	CustomerOrderItemID int IDENTITY (1,1),
	CustomerOrderID int,
	Qty int,
	UpdatedQty int
	)
GO

Create TRIGGER trgCustomerOrderItem
ON CustomerOrderItem,Product
FOR INSERT, UPDATE AS
	DECLARE @TheCustomerOrderID int
	DECLARE @TheQTY int
	Select @TheCustomerOrderID = CustomerID from inserted
	Select @TheQTY = QTY from inserted
	Select @OnHandQTY = QTY from Product
	INSERT INTO CustomerOrderItemLog (CustomerOrderItemID, CustomerOrderID,Qty,UpdatedQty)
		Values(

推荐答案

关于第一个触发器，它看起来很好，并且它确实什么都需要，那么就可以了.如果您愿意，可以稍微简化一下，例如:

About the first trigger, it looks just fine and id it does what is required, then it should be ok. If you like, you can simplify it a bit, for example:

Create TRIGGER trgCustomerInsert
ON Customer
FOR INSERT AS
    INSERT INTO CustomerLog (CustomerLogID, CustomerID, Email, LogDate)
    SELECT 'CustomerLogID', TheCustomerID, Email, GetDate()
    FROM inserted

关于第二个触发器，如果​​问题在于如何更新另一个表，就像您插入另一个表一样.您能否再描述一下所需的行为?

附加:

根据您的评论，您不应在产品"表上使用第二个触发器.如果CustomerOrderItem条目减去了数量，则您的触发器可能类似于:

About the second trigger, if the problem is how to update another table just like you insert into another table. Could you describe a bit more what''s the desired behavior?

ADDITION:

Based on your comment, you shouldn''t use the second trigger on Product table. If the CustomerOrderItem entry is subtracting the qauntity, then your trigger could be something like:

Create TRIGGER trgCustomerOrderItem
ON CustomerOrderItem
FOR INSERT, UPDATE AS
    UPDATE Product SET QTY = QTY - (SELECT SUM(QTY) FROM inserted)
    WHERE here define the condition for correct product based on CustomerOrderItem???

因此，您可以根据插入的行减少产品数量.我在CustomerOrderItem表上没有看到任何产品信息，所以我不知道更新的加入条件.也许在订单"表中.


触发器就像一个存储过程，一堆在触发动作发生时运行的T-SQL语句.您应该通过的链接很少:
- DML触发器 [创建触发器 [ DML触发器的多行注意事项 [

So you reduce the quantity of the product based on the inserted rows. I didn''t see any product information on CustomerOrderItem table so I didnät know the joining condition for the update. Perhaps it''s in the Order table.


The triggers are just like for example a stored procedure, a bunch of T-SQL statements that are run when the triggering action occurs. Few links you should go through:
- DML Triggers[^]
- CREATE TRIGGER[^]
- Multirow Considerations for DML Triggers[^]

这篇关于SQL帮助中的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！