Create Trigger Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create Trigger Problem

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-10-03 : 15:30:07

Could anyone explain why I get the following error
message when I try to apply the following code when managing triggers in Enterprise Manager, even though the syntax appears

correct.

Incorrect Syntax near GO
ALTER TRIGGER must be the first statement in a query batch.

If EXISTS (SELECT name FROM sysobjects WHERE name = 'INSERT_QTY' AND type = 'TR')
DROP TRIGGER INSERT_QTY

GO

CREATE TRIGGER INSERT_QTY ON [dbo].[ORD_HEADER]
FOR INSERT, UPDATE
AS

Declare @actionold VARCHAR(1), @actionnew VARCHAR(1), @orderNo INT, @userKey1 VARCHAR(20)

Set @userKey1 = (SELECT OH_USER1 FROM Inserted)
Set @actionold = (SELECT OH_LAST_ACTION FROM Deleted)
Set @actionnew = (SELECT OH_LAST_ACTION FROM Inserted)
Set @orderNo = (SELECT OH_ORDER_NUMBER FROM Inserted)

If @userKey1 = 'TENDER' Return
If @actionold = 'D' AND @actionnew = 'D' Return
If @actionold = 'D' AND @actionnew = 'I' Return
If @actionold = 'I' AND @actionnew = 'I' Return

Else

BEGIN

EXEC PROCESS_QTY @orderNo
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 15:33:50

Do not do this inside Enterprise Manager. Do it in Query Analyzer. Enterprise Manager does ALTERS even though you have specified DROP and CREATE.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 15:37:01

Well....

First, you have to realize that there may be many records in INSERTED or DELETED...

So....by doing what your doing will only give you the "last" (what a relative term) one...

What are you trying to do? No tech talk please....

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 15:46:26

Good catch Brett. I didn't even read the code, I just answered the question.

Pauliwebb, your trigger will not work if there are more than one record that were inserted or deleted. A variable can only hold one value at a time.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 15:52:38

If it did compile...it probably would work....which probably bad, because it wouldn't be doing the right thing....



DECLARE @Name sysname

SELECT COUNT(*) AS Result_Set
  FROM sysobjects

SELECT @Name = name 
  FROM sysobjects

SELECT @Name As Who_knows_what_Order_Name

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-10-03 : 15:53:23

Thanks Tara

Problem solved.
It's obvious that I'm too dependant on Enterprise Manager

Paul

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 15:56:04

quote:
Originally posted by pauliwebb

Thanks Tara

Problem solved.
It's obvious that I'm too dependant on Enterprise Manager

Paul

I don't think so.....Yes to too dependant on EM, No to problem solved...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 15:57:21

Paul, your error is solved but your trigger is not correct.

Tara

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-10-03 : 16:25:45

Hi

I was under the impression that the trigger only fires once if you are inserting or updating a single record.
The deleted table contains all the fields from the old record and the inserted table contains the fields from the new record.

When you say "A variable can only hold one value at a time." In what context are you applying it to my code.

Paul

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 16:36:52

The trigger fires for the entire SQL statement was run. So if you run:

DELETE FROM Table1 WHERE Column1 = 0

and there are 40 records that will be deleted, then 40 records will be in the deleted table for the trigger.

So, for this part of your code:
Set @userKey1 = (SELECT OH_USER1 FROM Inserted)
Set @actionold = (SELECT OH_LAST_ACTION FROM Deleted)
Set @actionnew = (SELECT OH_LAST_ACTION FROM Inserted)
Set @orderNo = (SELECT OH_ORDER_NUMBER FROM Inserted)

the variables will only hold the 40th record (using my example here).

Tara

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-10-03 : 16:47:14

Tara

Assuming then that I am only putting my trigger on the ORDER HEADER table.
If I insert a new ORDER, containing fields (1,2,3, etc), my Deleted table should be empty, but the Inserted table should only contain one record for each of fields (1,2,3 etc)
Is this correct or am I misinterprating your answer.

Paul

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 16:51:36

Yes you are correct. But you need to be aware that your triggers fires for all inserts and deletes on that table. So if someone inserts multiple records in one query or deletes mulitple records in one query, then your trigger isn't going to work correctly.

Tara

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-10-03 : 16:56:53

Based on that assumption is my code still incorrect, or do I need to alter it in any way.

Paul

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 16:59:47

Your code is incorrect if EVER there will be an INSERT or a DELETE statement that will affect multiple rows. Your trigger will ONLY work if it affects one record only.

Even if someone is in Query Analyzer running a query, the trigger will fire. So if they run my example and it deletes 40 rows, your trigger will not work properly. If your application has the ability to do something similar, it will not work properly either.

Tara

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-10-03 : 17:08:41

Sorry to appear thick, but I just want to get this right.

If I update 3 ORDERS(3 records) at once. Will my trigger fire 3 seperate times
once for each insert, or just once for all 3.

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-10-03 : 17:08:51

Sorry to appear thick, but I just want to get this right.

If I update 3 ORDERS(3 records) at once. Will my trigger fire 3 seperate times
once for each insert, or just once for all 3.

Paul

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-10-03 : 17:31:44

Tara
Don't bother to reply to my last message.
I answered the question in my reply.
The trigger is on the table not on the rows, so my last example would give man inserted table with 3 rows in.

P.S Many thanks for your help

Paul

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 21:37:15

quote:
Originally posted by pauliwebb
The trigger is on the table not on the rows, so my last example would give man inserted table with 3 rows in.

The trigger is on the rows, not on the table...

My car runs, but not on gasoline....

Your car won't run far with the "last" (and again, what a realtive term) Drop of gas...

No?

Damn, It's the Rob conflaguration....

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources