Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need a sample SQL Trigger for DDL statement that are execute on database

Posted on 2012-08-12
6
Medium Priority
?
690 Views
Last Modified: 2012-08-16
Need a sample SQL Trigger for DDL statement that are execute on database
0
Comment
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 38285676
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 38285780
sorry i missed to mention "truncate" statement. I want to capture only truncate statement exceute on my database.
0
 
LVL 12

Expert Comment

by:Tony303
ID: 38286522
Isn't a truncate statement a DML Statement
http://msdn.microsoft.com/en-us/library/ms189599(v=sql.105).aspx

The "truncation" trigger may not be possible...
see
http://msdn.microsoft.com/en-us/library/ms189799.aspx

specifically....

Although a TRUNCATE TABLE statement is in effect a DELETE statement, it does not activate a trigger because the operation does not log individual row deletions. However, only those users with permissions to execute a TRUNCATE TABLE statement need be concerned about inadvertently circumventing a DELETE trigger this way.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Accepted Solution

by:
TempDBA earned 2000 total points
ID: 38286709
Hey Tony,
       Truncate is not a DML but DDL statement.

Vivekanandhan,
       As expert Tony replied, there is no feasibility of creating a trigger for truncate statement. Again, you have a sample for creating a DDL trigger as provided in the link. You can modify it as per your requirement and test it anytime.
0
 
LVL 12

Expert Comment

by:Tony303
ID: 38286758
Temp
I see you are right DDL for Truncate.
I think that is incorrect...but hey... bigger brains than mine have decreed it so.

The way I see it... Truncate is manipulating the data, not changing the structure of the table. Hence DML not DDL.

T
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38288610
Check out the msdn for truncate in the following link. You will find the command under DDL (on your left hand side panel)
http://msdn.microsoft.com/en-us/library/ms177570.aspx
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question