Triggers are for INSERT, UPDATE, or DELETE only.

SanjayAttray (2/6/2010)

---

Triggers are for INSERT, UPDATE, or DELETE only.

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

Christian Buettner-167247 (2/5/2010)

---

I wonder why there is no DDL trigger available to capture this of event...

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.

petertrast (3/3/2010)

---

For DML, yes...

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

petertrast (3/3/2010)

---

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

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

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.

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