Trigger Question - 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)

Trigger Question

Author

Topic

poser
Posting Yak Master

124 Posts

Posted - 2008-12-11 : 12:42:53

I have a table with two columns
BeginDate EndDate

I ran a script to update BeginDate throught Query Analyzer(couple hundred rows were updated)
Say a BeginDate was originally 11\30\2008 and I update it to 12/31/2009.
There is a trigger that says when the BeginDate is updated mark the EndDate = to the update + 30 days.

It works when I update it through the program..Do triggers fire through the Query Analyzer?

Thanks for your help
R/P

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-11 : 13:23:05

first, you could look in BOL

second, triggers, like tables, are database objects

unlike tables they "fire" upon a specific DML operation (update, inerts, delete)

The only control you have over triggers is how you code them

the only way I know to see them in action is through a trace

did that help any?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

poser
Posting Yak Master

124 Posts

Posted - 2008-12-11 : 18:33:27

I did look at BOL but I'm still confused.
Maybe more info would help...

First I update table1.BeginDate from Table3.CACDate Where
Table3.Email = Table1.Email

UPDATE Table1
SET
Table1.BeginDate=Table3.CACDate
FROM Table1
INNER JOIN Table3
ON Table3.EMail=Table1.Email

So After I update Table1.BeginDate shouldn't the following
Trigger be shot off for every Table1.BeginDate that I update it should then update Table2.EndDate?

Here is the trigger on Table1:

Declare
@id int,
@BeginDate datetime

SELECT @id = EmployeeID,
@BeginDate = BeginDate
FROM inserted

If Update (BeginDate)
BEGIN
UPDATE Table2
SET EndDate = (@BeginDate + 30)
WHERE EmployeeID = @id
END

Again, Thanks for any help\responses
R\P

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-11 : 18:36:32

Triggers fire once per batch. So if your batch modified more than one row, the trigger still only fires once. See my blog for more info: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-12 : 10:28:17

did you come from db2 land?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources