Triggers in SQL Help

Question

0.00/5 (No votes)

See more:

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.

SQL

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

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.

SQL

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(

Posted 18-Dec-11 8:09am

Anthony Bond

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-12-18T08:28:00

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:

SQL

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

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:

SQL

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???

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[^]