Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

TRUNCATE Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 12:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:31 PM
Points: 167, Visits: 409
Then, is there any way to fire the Trigger upon this Truncate action, as well?





In ' Thoughts ',
Lonely Vampire


--In 'thoughts'...
Lonely Rogue
Post #861447
Posted Monday, February 8, 2010 7:17 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 605, Visits: 1,691
Triggers are for INSERT, UPDATE, or DELETE only.


That is true, but not (I think) the purpose of today's QotD.

DELETE operates on the individual rows of a table, whereas TRUNCATE operates on the data pages. As each is logged differently there is no information about individual rows for the trigger to reference upon a TRUNCATE statement.
Post #862162
Posted Monday, February 8, 2010 10:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 2, 2011 3:22 AM
Points: 1,227, Visits: 154
"TRUE or FASLE" - good question but in need of a spell check!

A simple experiment with "fasle" shows that the spell check ignores words in capitals!
Post #862211
Posted Monday, February 8, 2010 11:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
hey Clive...good catch..
Post #862216
Posted Wednesday, February 10, 2010 3:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
SanjayAttray (2/6/2010)
Triggers are for INSERT, UPDATE, or DELETE only.

Correction:
DML triggers are for INSERT, UPDATE, or DELETE only.

There are also DDL triggers for a wide variety of DDL events. I answered wrong because I thought that TRUNCATE could generate a DDL event
Actually TRUNCATE looks like a DDL statement, because it requests a schema modification lock:

CREATE TABLE test (a INT)

BEGIN TRANSACTION

TRUNCATE TABLE test

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND resource_type = 'OBJECT'
AND resource_associated_entity_id = OBJECT_ID('test')

ROLLBACK

DROP TABLE test

In the example above, we will see request_mode = Sch-M (schema modification). This is specific to DDL events.
Post #863051
Posted Wednesday, March 3, 2010 11:13 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
Christian Buettner-167247 (2/5/2010)
I wonder why there is no DDL trigger available to capture this of event...


Because it is a DML event, the structure of the table is not changing, just the data in it? Sound right experts?


Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #876208
Posted Wednesday, March 3, 2010 11:16 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
SanjayAttray (2/6/2010)
Back to basics. Most of the time I select wrong answer for basic and simple question. Triggers are for INSERT, UPDATE, or DELETE only.


For DML, yes...

Or... CREATE, ALTER, etc.... for DDL...


Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #876212
Posted Wednesday, March 3, 2010 11:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
petertrast (3/3/2010)
SanjayAttray (2/6/2010)
Back to basics. Most of the time I select wrong answer for basic and simple question. Triggers are for INSERT, UPDATE, or DELETE only.


For DML, yes...

Or... CREATE, ALTER, etc.... for DDL...


OK, I promise to read all posts before replying


Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #876214
Posted Wednesday, March 3, 2010 1:43 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
petertrast (3/3/2010)
Christian Buettner-167247 (2/5/2010)
I wonder why there is no DDL trigger available to capture this of event...


Because it is a DML event, the structure of the table is not changing, just the data in it?

By the way, Oracle documentation contains an explicit list of DDL statements: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_1001.htm#sthref2301. TRUNCATE is in that list. So, at least in Oracle it is not a DML event.

Unfortunately, I didn't find the similar list for MSSQL. However, these two quotes from BOL implicitly say that TRUNCATE belongs to DDL:
http://msdn.microsoft.com/en-us/library/ms177570.aspx
TRUNCATE TABLE (Transact-SQL)

Permissions
--------------------------------------------------------------------------------
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles


OK, who's that guy with db_ddladmin permissions? http://msdn.microsoft.com/en-us/library/ms189121.aspx
Database-level role name: db_ddladmin
Description: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.


So, db_ddladmin can run any DDL command; db_ddladmin can run TRUNCATE TABLE. This means TRUNCATE is a DDL command, I believe
Post #876335
Posted Tuesday, March 30, 2010 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
vk-kirov (3/3/2010)
So, db_ddladmin can run any DDL command; db_ddladmin can run TRUNCATE TABLE. This means TRUNCATE is a DDL command, I believe

Crows are birds.
All crows are black.
Therefore, all birds are black.

Oh dear.





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892890
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse