Condividi tramite


Gruppi di eventi DDL

Nelle tabelle seguenti vengono elencati i gruppi di eventi DLL che è possibile utilizzare per eseguire un trigger DDL o una notifica degli eventi e vengono indicate le istruzioni Transact-SQL incluse. Si noti la natura inclusiva dei gruppi di eventi. Ad esempio, un trigger DDL o una notifica degli eventi che specifica FOR DDL_TABLE_EVENTS (10018) include le istruzioni Transact-SQL CREATE TABLE, ALTER TABLE e DROP TABLE. In un trigger DDL o una notifica degli eventi che consente di specificare FOR DDL_TABLE_VIEW_EVENTS (type 10017) sono incluse tutte le istruzioni Transact-SQL nei tipi DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_INDEX_EVENTS e DDL_STATISTICS_EVENTS.

[!NOTA]

Alcune stored procedure di sistema che eseguono operazioni di tipo DDL possono inoltre attivare trigger DDL o notifiche degli eventi. Testare i trigger DDL e le notifiche degli eventi per determinarne la risposta alle stored procedure di sistema eseguite. Ad esempio, l'istruzione CREATE TYPE e la stored procedure sp_addtype consentono entrambe di attivare un trigger DDL o una notifica degli eventi creata in un evento CREATE_TYPE.

Eventi

Gli eventi elencati in DDL_DATABASE_LEVEL_EVENTS vengono eseguiti a livello di server (istanza) o di database. Gli eventi elencati in DDL_SERVER_LEVEL_EVENTS vengono eseguiti solo a livello di server.

parent_type

tipo

name

NULL

296

ALTER_SERVER_CONFIGURATION

NULL

10001

DDL_EVENTS

10001

10016

|     DDL_DATABASE_LEVEL_EVENTS

10016

10027

|    |    DDL_ASSEMBLY_EVENTS

10027

102

|    |    |    ALTER_ASSEMBLY

10027

101

|    |    |    CREATE_ASSEMBLY

10027

103

|    |    |    DROP_ASSEMBLY

10016

10029

|    |    DDL_DATABASE_SECURITY_EVENTS

10029

10033

|    |    |    DDL_APPLICATION_ROLE_EVENTS

10033

138

|    |    |    |    ALTER_APPLICATION_ROLE

10033

137

|    |    |    |    CREATE_APPLICATION_ROLE

10033

139

|    |    |    |    DROP_APPLICATION_ROLE

10029

10038

|    |    |    DDL_ASYMMETRIC_KEY_EVENTS

10038

248

|    |    |    |    ALTER_ASYMMETRIC_KEY

10038

247

|    |    |    |    CREATE_ASYMMETRIC_KEY

10038

249

|    |    |    |    DROP_ASYMMETRIC_KEY

10029

10036

|    |    |    DDL_AUTHORIZATION_DATABASE_EVENTS

10036

205

|    |    |    |    ALTER_AUTHORIZATION_DATABASE

10029

10030

|    |    |    DDL_CERTIFICATE_EVENTS

10030

198

|    |    |    |    ALTER_CERTIFICATE

10030

197

|    |    |    |    CREATE_CERTIFICATE

10030

199

|    |    |    |    DROP_CERTIFICATE

10029

10039

|    |    |    DDL_CRYPTO_SIGNATURE_EVENTS

10039

257

|    |    |    |    ADD_SIGNATURE

10039

255

|    |    |    |    ADD_SIGNATURE_SCHEMA_OBJECT

10039

258

|    |    |    |    DROP_SIGNATURE

10039

256

|    |    |    |    DROP_SIGNATURE_SCHEMA_OBJECT

10029

10066

|    |    |    DDL_DATABASE_AUDIT_SPECIFICATION_EVENTS

10066

291

|    |    |    |    ALTER_DATABASE_AUDIT_SPECIFICATION

10066

290

|    |    |    |    CREATE_DATABASE_AUDIT_SPECIFICATION

10066

292

|    |    |    |    DROP_DATABASE_AUDIT_SPECIFICATION

10029

10062

|    |    |    DDL_DATABASE_ENCRYPTION_KEY_EVENTS

10062

279

|    |    |    |    ALTER_DATABASE_ENCRYPTION_KEY

10062

278

|    |    |    |    CREATE_DATABASE_ENCRYPTION_KEY

10062

280

|    |    |    |    DROP_DATABASE_ENCRYPTION_KEY

10029

10035

|    |    |    DDL_GDR_DATABASE_EVENTS

10035

171

|    |    |    |    DENY_DATABASE

10035

170

|    |    |    |    GRANT_DATABASE

10035

172

|    |    |    |    REVOKE_DATABASE

10029

10040

|    |    |    DDL_MASTER_KEY_EVENTS

10040

253

|    |    |    |    ALTER_MASTER_KEY

10040

252

|    |    |    |    CREATE_MASTER_KEY

10040

254

|    |    |    |    DROP_MASTER_KEY

10029

10032

|    |    |    DDL_ROLE_EVENTS

10032

207

|    |    |    |    ADD_ROLE_MEMBER

10032

135

|    |    |    |    ALTER_ROLE

10032

134

|    |    |    |    CREATE_ROLE

10032

136

|    |    |    |    DROP_ROLE

10032

208

|    |    |    |    DROP_ROLE_MEMBER

10029

10034

|    |    |    DDL_SCHEMA_EVENTS

10034

142

|    |    |    |    ALTER_SCHEMA

10034

141

|    |    |    |    CREATE_SCHEMA

10034

143

|    |    |    |    DROP_SCHEMA

10029

10037

|    |    |    DDL_SYMMETRIC_KEY_EVENTS

10037

245

|    |    |    |    ALTER_SYMMETRIC_KEY

10037

244

|    |    |    |    CREATE_SYMMETRIC_KEY

10037

246

|    |    |    |    DROP_SYMMETRIC_KEY

10029

10031

|    |    |    DDL_USER_EVENTS

10031

132

|    |    |    |    ALTER_USER

10031

131

|    |    |    |    CREATE_USER

10031

133

|    |    |    |    DROP_USER

10016

10052

|    |    DDL_DEFAULT_EVENTS

10052

218

|    |    |    BIND_DEFAULT

10052

220

|    |    |    CREATE_DEFAULT

10052

231

|    |    |    DROP_DEFAULT

10052

242

|    |    |    UNBIND_DEFAULT

10016

10026

|    |    DDL_EVENT_NOTIFICATION_EVENTS

10026

74

|    |    |    CREATE_EVENT_NOTIFICATION

10026

76

|    |    |    DROP_EVENT_NOTIFICATION

10016

10053

|    |    DDL_EXTENDED_PROPERTY_EVENTS

10053

211

|    |    |    ALTER_EXTENDED_PROPERTY

10053

222

|    |    |    CREATE_EXTENDED_PROPERTY

10053

233

|    |    |    DROP_EXTENDED_PROPERTY

10016

10054

|    |    DDL_FULLTEXT_CATALOG_EVENTS

10054

212

|    |    |    ALTER_FULLTEXT_CATALOG

10054

223

|    |    |    CREATE_FULLTEXT_CATALOG

10054

234

|    |    |    DROP_FULLTEXT_CATALOG

10016

10067

|    |    DDL_FULLTEXT_STOPLIST_EVENTS

10067

294

|    |    |    ALTER_FULLTEXT_STOPLIST

10067

293

|    |    |    CREATE_FULLTEXT_STOPLIST

10067

295

|    |    |    DROP_FULLTEXT_STOPLIST

10016

10023

|    |    DDL_FUNCTION_EVENTS

10023

62

|    |    |    ALTER_FUNCTION

10023

61

|    |    |    CREATE_FUNCTION

10023

63

|    |    |    DROP_FUNCTION

10016

10049

|    |    DDL_PARTITION_EVENTS

10049

10050

|    |    |    DDL_PARTITION_FUNCTION_EVENTS

10050

192

|    |    |    |    ALTER_PARTITION_FUNCTION

10050

191

|    |    |    |    CREATE_PARTITION_FUNCTION

10050

193

|    |    |    |    DROP_PARTITION_FUNCTION

10049

10051

|    |    |    DDL_PARTITION_SCHEME_EVENTS

10051

195

|    |    |    |    ALTER_PARTITION_SCHEME

10051

194

|    |    |    |    CREATE_PARTITION_SCHEME

10051

196

|    |    |    |    DROP_PARTITION_SCHEME

10016

10055

|    |    DDL_PLAN_GUIDE_EVENTS

10055

216

|    |    |    ALTER_PLAN_GUIDE

10055

228

|    |    |    CREATE_PLAN_GUIDE

10055

238

|    |    |    DROP_PLAN_GUIDE

10016

10024

|    |    DDL_PROCEDURE_EVENTS

10024

52

|    |    |    ALTER_PROCEDURE

10024

51

|    |    |    CREATE_PROCEDURE

10024

53

|    |    |    DROP_PROCEDURE

10016

10056

|    |    DDL_RULE_EVENTS

10056

219

|    |    |    BIND_RULE

10056

229

|    |    |    CREATE_RULE

10056

239

|    |    |    DROP_RULE

10056

243

|    |    |    UNBIND_RULE

10016

10069

|    |    DDL_SEARCH_PROPERTY_LIST_EVENTS

10069

298

|    |    |    ALTER_SEARCH_PROPERTY_LIST

10069

297

|    |    |    CREATE_SEARCH_PROPERTY_LIST

10069

299

|    |    |    DROP_SEARCH_PROPERTY_LIST

10016

10070

|    |    DDL_SEQUENCE_EVENTS

10070

304

|    |    |    ALTER_SEQUENCE

10070

303

|    |    |    CREATE_SEQUENCE

10070

305

|    |    |    DROP_SEQUENCE

10016

10041

|    |    DDL_SSB_EVENTS

10041

10063

|    |    |    DDL_BROKER_PRIORITY_EVENTS

10063

282

|    |    |    |    ALTER_BROKER_PRIORITY

10063

281

|    |    |    |    CREATE_BROKER_PRIORITY

10063

283

|    |    |    |    DROP_BROKER_PRIORITY

10041

10043

|    |    |    DDL_CONTRACT_EVENTS

10043

154

|    |    |    |    CREATE_CONTRACT

10043

156

|    |    |    |    DROP_CONTRACT

10041

10042

|    |    |    DDL_MESSAGE_TYPE_EVENTS

10042

152

|    |    |    |    ALTER_MESSAGE_TYPE

10042

151

|    |    |    |    CREATE_MESSAGE_TYPE

10042

153

|    |    |    |    DROP_MESSAGE_TYPE

10041

10044

|    |    |    DDL_QUEUE_EVENTS

10044

158

|    |    |    |    ALTER_QUEUE

10044

157

|    |    |    |    CREATE_QUEUE

10044

159

|    |    |    |    DROP_QUEUE

10041

10047

|    |    |    DDL_REMOTE_SERVICE_BINDING_EVENTS

10047

175

|    |    |    |    ALTER_REMOTE_SERVICE_BINDING

10047

174

|    |    |    |    CREATE_REMOTE_SERVICE_BINDING

10047

176

|    |    |    |    DROP_REMOTE_SERVICE_BINDING

10041

10046

|    |    |    DDL_ROUTE_EVENTS

10046

165

|    |    |    |    ALTER_ROUTE

10046

164

|    |    |    |    CREATE_ROUTE

10046

166

|    |    |    |    DROP_ROUTE

10041

10045

|    |    |    DDL_SERVICE_EVENTS

10045

162

|    |    |    |    ALTER_SERVICE

10045

161

|    |    |    |    CREATE_SERVICE

10045

163

|    |    |    |    DROP_SERVICE

10016

10022

|    |    DDL_SYNONYM_EVENTS

10022

34

|    |    |    CREATE_SYNONYM

10022

36

|    |    |    DROP_SYNONYM

10016

10017

|    |    DDL_TABLE_VIEW_EVENTS

10017

10020

|    |    |    DDL_INDEX_EVENTS

10020

213

|    |    |    |    ALTER_FULLTEXT_INDEX

10020

25

|    |    |    |    ALTER_INDEX

10020

224

|    |    |    |    CREATE_FULLTEXT_INDEX

10020

24

|    |    |    |    CREATE_INDEX

10020

274

|    |    |    |    CREATE_SPATIAL_INDEX

10020

206

|    |    |    |    CREATE_XML_INDEX

10020

235

|    |    |    |    DROP_FULLTEXT_INDEX

10020

26

|    |    |    |    DROP_INDEX

10017

10021

|    |    |    DDL_STATISTICS_EVENTS

10021

27

|    |    |    |    CREATE_STATISTICS

10021

29

|    |    |    |    DROP_STATISTICS

10021

28

|    |    |    |    UPDATE_STATISTICS

10017

10018

|    |    |    DDL_TABLE_EVENTS

10018

22

|    |    |    |    ALTER_TABLE

10018

21

|    |    |    |    CREATE_TABLE

10018

23

|    |    |    |    DROP_TABLE

10017

10019

|    |    |    DDL_VIEW_EVENTS

10019

42

|    |    |    |    ALTER_VIEW

10019

41

|    |    |    |    CREATE_VIEW

10019

43

|    |    |    |    DROP_VIEW

10016

10025

|    |    DDL_TRIGGER_EVENTS

10025

72

|    |    |    ALTER_TRIGGER

10025

71

|    |    |    CREATE_TRIGGER

10025

73

|    |    |    DROP_TRIGGER

10016

10028

|    |    DDL_TYPE_EVENTS

10028

91

|    |    |    CREATE_TYPE

10028

93

|    |    |    DROP_TYPE

10016

10048

|    |    DDL_XML_SCHEMA_COLLECTION_EVENTS

10048

178

|    |    |    ALTER_XML_SCHEMA_COLLECTION

10048

177

|    |    |    CREATE_XML_SCHEMA_COLLECTION

10048

179

|    |    |    DROP_XML_SCHEMA_COLLECTION

10016

241

|    |    RENAME

10001

10002

|    DDL_SERVER_LEVEL_EVENTS

10002

214

|    |    ALTER_INSTANCE

10002

10071

|    |    DDL_AVAILABILITY_GROUP_EVENTS

10071

307

|    |    |    ALTER_AVAILABILITY_GROUP

10071

306

|    |    |    CREATE_AVAILABILITY_GROUP

10071

308

|    |    |    DROP_AVAILABILITY_GROUP

10002

10004

|    |    DDL_DATABASE_EVENTS

10004

202

|    |    |    ALTER_DATABASE

10004

201

|    |    |    CREATE_DATABASE

10004

203

|    |    |    DROP_DATABASE

10002

10003

|    |    DDL_ENDPOINT_EVENTS

10003

182

|    |    |    ALTER_ENDPOINT

10003

181

|    |    |    CREATE_ENDPOINT

10003

183

|    |    |    DROP_ENDPOINT

10002

10057

|    |    DDL_EVENT_SESSION_EVENTS

10057

265

|    |    |    ALTER_EVENT_SESSION

10057

264

|    |    |    CREATE_EVENT_SESSION

10057

266

|    |    |    DROP_EVENT_SESSION

10002

10011

|    |    DDL_EXTENDED_PROCEDURE_EVENTS

10011

221

|    |    |    CREATE_EXTENDED_PROCEDURE

10011

232

|    |    |    DROP_EXTENDED_PROCEDURE

10002

10012

|    |    DDL_LINKED_SERVER_EVENTS

10012

263

|    |    |    ALTER_LINKED_SERVER

10012

225

|    |    |    CREATE_LINKED_SERVER

10012

10013

|    |    |    DDL_LINKED_SERVER_LOGIN_EVENTS

10013

226

|    |    |    |    CREATE_LINKED_SERVER_LOGIN

10013

236

|    |    |    |    DROP_LINKED_SERVER_LOGIN

10012

262

|    |    |    DROP_LINKED_SERVER

10002

10014

|    |    DDL_MESSAGE_EVENTS

10014

215

|    |    |    ALTER_MESSAGE

10014

227

|    |    |    CREATE_MESSAGE

10014

237

|    |    |    DROP_MESSAGE

10002

10015

|    |    DDL_REMOTE_SERVER_EVENTS

10015

217

|    |    |    ALTER_REMOTE_SERVER

10015

230

|    |    |    CREATE_REMOTE_SERVER

10015

240

|    |    |    DROP_REMOTE_SERVER

10002

10058

|    |    DDL_RESOURCE_GOVERNOR_EVENTS

10058

273

|    |    |    ALTER_RESOURCE_GOVERNOR_CONFIG

10058

10059

|    |    |    DDL_RESOURCE_POOL

10059

268

|    |    |    |    ALTER_RESOURCE_POOL

10059

267

|    |    |    |    CREATE_RESOURCE_POOL

10059

269

|    |    |    |    DROP_RESOURCE_POOL

10058

10060

|    |    |    DDL_WORKLOAD_GROUP

10060

271

|    |    |    |    ALTER_WORKLOAD_GROUP

10060

270

|    |    |    |    CREATE_WORKLOAD_GROUP

10060

272

|    |    |    |    DROP_WORKLOAD_GROUP

10002

10005

|    |    DDL_SERVER_SECURITY_EVENTS

10005

209

|    |    |    ADD_SERVER_ROLE_MEMBER

10005

301

|    |    |    ALTER_SERVER_ROLE

10005

300

|    |    |    CREATE_SERVER_ROLE

10005

10008

|    |    |    DDL_AUTHORIZATION_SERVER_EVENTS

10008

204

|    |    |    |    ALTER_AUTHORIZATION_SERVER

10005

10009

|    |    |    DDL_CREDENTIAL_EVENTS

10009

260

|    |    |    |    ALTER_CREDENTIAL

10009

259

|    |    |    |    CREATE_CREDENTIAL

10009

261

|    |    |    |    DROP_CREDENTIAL

10005

10061

|    |    |    DDL_CRYPTOGRAPHIC_PROVIDER_EVENTS

10061

276

|    |    |    |    ALTER_CRYPTOGRAPHIC_PROVIDER

10061

275

|    |    |    |    CREATE_CRYPTOGRAPHIC_PROVIDER

10061

277

|    |    |    |    DROP_CRYPTOGRAPHIC_PROVIDER

10005

10007

|    |    |    DDL_GDR_SERVER_EVENTS

10007

168

|    |    |    |    DENY_SERVER

10007

167

|    |    |    |    GRANT_SERVER

10007

169

|    |    |    |    REVOKE_SERVER

10005

10006

|    |    |    DDL_LOGIN_EVENTS

10006

145

|    |    |    |    ALTER_LOGIN

10006

144

|    |    |    |    CREATE_LOGIN

10006

146

|    |    |    |    DROP_LOGIN

10005

10064

|    |    |    DDL_SERVER_AUDIT_EVENTS

10064

285

|    |    |    |    ALTER_SERVER_AUDIT

10064

284

|    |    |    |    CREATE_SERVER_AUDIT

10064

286

|    |    |    |    DROP_SERVER_AUDIT

10005

10065

|    |    |    DDL_SERVER_AUDIT_SPECIFICATION_EVENTS

10065

288

|    |    |    |    ALTER_SERVER_AUDIT_SPECIFICATION

10065

287

|    |    |    |    CREATE_SERVER_AUDIT_SPECIFICATION

10065

289

|    |    |    |    DROP_SERVER_AUDIT_SPECIFICATION

10005

10010

|    |    |    DDL_SERVICE_MASTER_KEY_EVENTS

10010

251

|    |    |    |    ALTER_SERVICE_MASTER_KEY

10005

302

|    |    |    DROP_SERVER_ROLE

10005

210

|    |    |    DROP_SERVER_ROLE_MEMBER

I dati sopra riportati possono essere creati eseguendo l'esempio di codice riportato di seguito.

WITH DirectReports(name, parent_type, type, level, sort) AS 
(
    SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)
    FROM sys.trigger_event_types 
    WHERE parent_type IS NULL
    UNION ALL
    SELECT  CONVERT(varchar(255), REPLICATE ('|   ' , level) + e.type_name),
        e.parent_type, e.type, level + 1,
    CONVERT (varchar(255), RTRIM(sort) + '|   ' + e.type_name)
    FROM sys.trigger_event_types AS e
        INNER JOIN DirectReports AS d
        ON e.parent_type = d.type 
)
SELECT parent_type, type, name
FROM DirectReports
ORDER BY sort;

Vedere anche

Concetti

Notifiche degli eventi

Trigger DDL

Eventi DDL