Stack Overflow
Stack Overflow

Questions tagged [ddl-trigger]

DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

44 questions
23
votes
3 answers

execute a trigger when I create a table

I would like to know if a trigger on a system table of PostgreSQL can be executed when I create a table I need to add 2 functions on each table of my database and I would like to do it dynamically Thanks
Julien
  • 401
  • 1
  • 4
  • 9
4
votes
1 answer

Fire trigger on DISABLE TRIGGER

There's a safety trigger that blocks all SQL DDL events (ALTER/DROP/CREATE etc.) on a production database instance. For deployments you'd do DISABLE TRIGGER and then ENABLE TRIGGER when done. I'd like an Operator to be notified (EXEC…
Serguei
  • 2,910
  • 3
  • 24
  • 34
4
votes
3 answers

SQL Server DDL trigger on drop table

I have a database with few tables: tab1, tab2, tab3. How can I create a DDL trigger on drop table only for tab2 (not for tab1 and tab3). When drop tab2 is called I need to update values in this table but not to delete tab2. How can I do this? I…
user3403730
  • 101
  • 1
  • 2
  • 11
4
votes
1 answer

SQL Server track DDL CREATE USER

I am trying to track user creation. I have looked at the DDL triggers in many posts but those seem only to track objects, not users. Is there a way for me to track/record when a user is created or deleted in SQL Server?
Kristin Kasmarcak Georgi
  • 41
  • 2
4
votes
1 answer

Understanding SQL Server Audit and the .sqlaudit logfile

Re: SQL Server AUDIT component of SQL Server 2008 EE or SQL Server 2012 EE 1) Has anyone been able to produce a test harness (ie. T-SQL) that maps what DDL statements trigger what Audit Events? Eg. DROP TABLE [table-name] triggers the…
Mark
  • 687
  • 7
  • 12
4
votes
2 answers

use function from another database in computed column

I have a set of custom CLR functions installed in single database. When I need to use these functions in queries/views/procedures on another database, I simply reference them by three-part name [db].[schema].[function]() and it works fine. But I…
AdamL
  • 12,421
  • 5
  • 50
  • 74
4
votes
1 answer

How to get procedure text before ALTER from DDL trigger

I am creating a trigger to track how procedure text has been ALTERed. Inside a database DDL trigger, it's possible to access current procedure Text through /EVENT_INSTANCE/TSQLCommand. Even after investigating EVENTDATA(), it did not contain values…
dance2die
  • 35,807
  • 39
  • 131
  • 194
4
votes
4 answers

SQL server schema auditing?

We have a SQL Server 2008 Enterprise database with two different schemas, a locked one that we maintain and an open one that we allow outside development teams to add to and modify for their own needs. Usually this works out OK for us but one…
snappymcsnap
  • 2,050
  • 2
  • 29
  • 53
3
votes
1 answer

MySql: Know the date when the table was last modified

I'm using a InnoDB engine in MySql and I'm trying to figure out if it is possible to know the datetime of the last time a table was modified (be it it's data or it's structure). Since it's InnoDB, I cannot use the column update_time in…
Mathieu
  • 1,638
  • 2
  • 27
  • 50
3
votes
2 answers

SQL CLR DDL trigger written on C#

Is it possible to create a database/DDL trigger in C#? And, if so, then how? [EDIT] Basically, I decided to use CLR for the database trigger because I want to utilize C# SMO to script the objects that change and insert the object script into a…
bitcycle
  • 7,632
  • 16
  • 70
  • 121
3
votes
1 answer

Single out specific parts of EVENTDATA for use in a DDL Trigger?

Current Trigger is as follows: CREATE TRIGGER TestTrigger ON DATABASE FOR ALTER_TABLE AS BEGIN SET NOCOUNT ON; DECLARE @FULL_STATEMENT SYSNAME SELECT @FULL_STATEMENT =…
Ambler
  • 43
  • 5
3
votes
1 answer

Create a TRIGGER to create a TRIGGER when a table is created

I am interested in creating a TRIGGER that creates a TRIGGER when a table is created. Poking around in mysql databases, I noticed that the tables for a schema are returned with: select TABLE_NAME, TABLE_SCHEMA from information_schema.TABLES; Is the…
brandeded
  • 2,080
  • 6
  • 25
  • 52
2
votes
2 answers

How to create DDL trigger to all databases in SQL Server 2005 instance

I am going to create a DDL trigger to all databases in SQL Server instance. I'd like to do it in one run instead of many runs for each database. Below are the two T-SQL statements I need to execute: -- Create table use GO CREATE TABLE…
titanium
  • 347
  • 2
  • 11
  • 21
2
votes
1 answer

How to get Database Name in a Logon SQL Trigger

How to get Database Name in a Logon Trigger tried several tsql code CREATE TRIGGER tr_stop_excel_users ON ALL SERVER FOR LOGON AS BEGIN IF (SELECT DB_NAME() FROM sys.databases) = 'TESTDB' and ORIGINAL_LOGIN() <> N'xx\xxxxxxx' AND APP_NAME() LIKE…
Sam
  • 21
  • 2
2
votes
1 answer

how to add signature to a DDL trigger?

I have a DDL trigger on a database. I want to add a signature to it, by certificate. I have full sa rights, but when I try to run the statement ADD SIGNATURE TO OBJECT::[objects_monitoring] BY CERTIFICATE [mail_certificate] WITH PASSWORD =…
Oliver
  • 668
  • 2
  • 7
  • 19
1
2 3