trouble with triggers - 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 2005 Forums

Other SQL Server Topics (2005)

trouble with triggers

Author

Topic

sarian
Starting Member

2 Posts

Posted - 2007-10-28 : 22:29:33

im having an issue with a trigger i have written, its set to add a date to a column based on the status of another however its not working unless both of the colums are set = 1 code below for trigger
any ideas or pointers would be greatly appreciated

ALTER TRIGGER [dbo].[fillsitecompletedate]
ON [dbo].[DisFull]
AFTER INSERT,UPDATE

AS
BEGIN UPDATE DisFull
SET Dispatchdate = GETDATE(), Sitecompletedate = GETDATE()
WHERE (Dispatched = 1) AND (Dispatchdate IS NULL) AND (Sitecomplete = 1) AND (Sitecompletedate IS NULL) AND (internalrec_ID =
(SELECT MAX(internalrec_ID) AS Expr1
FROM DisFull AS DisFull_1)) END
BEGIN
UPDATE DisFull
SET Sitecompletedate = GETDATE()
WHERE (Sitecomplete = 1)AND (Sitecompletedate IS NULL) AND (internalrec_ID =
(SELECT MAX(internalrec_ID) AS Expr1
FROM DisFull AS DisFull_1))
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-28 : 23:44:39

Your concept of trigger is wrong. Where is the inserted or deleted table in your trigger ?
Take you should take a look at this first http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

Also read the Books On Line more on trigger.

KH
[spoiler]Time is always against us[/spoiler]

sarian
Starting Member

2 Posts

Posted - 2007-10-29 : 00:40:09

i agree my perception of triggers is most likely incorrect however i didnt notice that my first statment was the issue removing all entrys reffering to the sitecomplete and sitecompletedate in my first stament makes the trigger function i did find the material you refrenced enlightening and appreciate your response however this is simply a concept project and not for production if only been working on it for about two days

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:53:38

Just in case you did not spot this point:

The second BEGIN/END is NOT part of the trigger, so when you run this script that will be run on the database, and not be included when the trigger itself fires.

Note that your trigger will fire, and update the whole table (where the query criteria apply) whenever any record is changed.

You may want to use the pseudo INSERTED and UPDATED tables to limit the scope of your update to only those record(s) changed which caused the trigger to fire.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources