Triggers 1

[fpgiv]

Hi,
I have a table called Weathermen that has a column called reviewed, and I want this field to be updated by a trigger. Whenever something is added to the Table, I want reviewed to automatically be set to No. Also, when an existing record is changed, I want reviewed to be set to No. I'm not familiar with triggers at all, so if someone could point me in the right direction, I'd appreciate it.
Thanks!!

 

[nigelrivett]

See

http://www.nigelrivett.net/Triggers_1_A_beginners_guide.html

You need to use the PK and the inserted table to update the rows

update Weathermen
set reviewed = 'no'
from Weathermen w
join inserted i
on w.pk = i.pk


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[fpgiv]

Hi nigelrivett,
I'm not clear on where the 'inserted i' part comes from, could you elaborate a bit more on that please?
Thanks.

 

[nigelrivett]

see the link in the previous post (or bol).
In a trigger you get an inserted and deleted table. The inserted holds a copy of the changed rows after the change and deleted before the change (except for blobs).
Do for and insert deleted is empty, for a delete inserted is empty, an update both have rows.
For an after trigger inserted will contain the rows in the table as the data update has already taken place.
You can join to the table from these tables using the PK to access the rows that have been affected.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[fpgiv]

Great. Thanks a bunch. I didn't quite get that from your earlier post, but that's clear now.

 

[donutman]

Fpgiv, you might want to consider just putting the "No" in the code that does the inserting of new records. This could be a Stored Proc or the application itself. It's better to avoid triggers if you can. If the application isn't setting a value at all, then another alternative is to make "No" the default value of the column, so that it is automatically initialized as "No" instead of Null.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]