I did not know this TRUNCATE and Triggers

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

SQL Server Development (2000)

I did not know this TRUNCATE and Triggers

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-27 : 15:34:49

I would have put money down the trigger would have fired




CREATE TABLE myTable00(Col1 int)
CREATE TABLE myTable99(Col1 int)
GO

CREATE TRIGGER myTrigge00 ON myTable00 FOR DELETE AS BEGIN INSERT INTO myTable99(Col1) SELECT Col1 FROM deleted END 
GO

INSERT INTO myTable00(Col1) 
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
GO

SELECT * FROM myTable00

DELETE FROM myTable00 WHERE Col1 = 1

TRUNCATE TABLE myTable00

SELECT * FROM myTable99
GO


DROP TABLE myTable00
DROP TABLE myTable99

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

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 15:39:49

You know that BCP won't fire them either (unless you ask nicely), eh?

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-27 : 15:57:21

yeah, I guess it makes sense, I mean if the log doesn't know, why should a trigger

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-27 : 17:40:24

In SQL 2000 you get only INSERT, UPDATE, or DELETE statement triggers.

In SQL 2005 you also get CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement triggers.

Still no TRUNCATE triggers.

CODO ERGO SUM

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 17:54:48

Doesnt look like its gonna make it into 2008 ether

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-27 : 18:16:41

I can understand why there is no trigger on a TRUNCATE. The idea is to make it run fast so you don't want any trigger getting in the way.

Maybe they could could add an option:
TRUNCATE TABLE MYTABLE WITH DO_NOT_EXECUTE_TRUNCATE_TRIGGER

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 20:03:26

"The idea is to make it run fast ..."

In that case I think your proposed syntax should be changed to:

TRUNCATE TABLE MYTABLE WITH PLEASE_EXECUTE_TRUNCATE_TRIGGER

pootle_flump

1064 Posts

Posted - 2007-09-28 : 12:12:59

Truncate is a DDL command anyway so certainly not captured in 2000 (nor 2005 either but at least it has DDL triggers).

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-28 : 13:58:01

OK, so does SELECT * INTO TABLE FROM TABLE2 affect triggers?

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-28 : 14:01:37

which trigger?
with SELECT * INTO TABLE FROM TABLE2
you're creating a table. so it can't have triggers yet.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-28 : 14:34:25

don't mind me, it's the ABS() talking

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-28 : 14:41:39

you mean thw absynth?

that's some good stuff!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-28 : 14:57:18

No, what does the function represent?

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-28 : 15:00:50

absolute. i probably don't get it then..

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources