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.
Questions tagged [ddl-trigger]
44 questions
1
vote
2 answers
SQL Server DDL changes (column names, types)
I need to audit DDL changes made to a database. Those changes need to be replicated in many other databases at a later time. I found here that one can enable DDL triggers to keep track of DDL activities, and that works great for create table and…
ferc
- 560
- 6
- 21
1
vote
2 answers
DDL Trigger For Logon Insert data to a table
I have a below DDL Trigger on my server
CREATE TRIGGER [DDLForLogin] ON ALL SERVER
FOR LOGON
AS BEGIN
DECLARE @data XML
SET @data = EVENTDATA()
IF EXISTS(SELECT * FROM sys.Databases WHERE NAME = 'DatabaseMaintenance') Begin
…
mehdi lotfi
- 11,194
- 18
- 82
- 128
1
vote
1 answer
Modify a Data Definition Language Trigger in SQL Server 2012
I have a database in SQL Server 2012, where I created a DDL trigger.
How can modify a data definition language trigger in SQL?
He don't have the 'Modify' option.
user2645263
- 31
- 6
1
vote
1 answer
Capture and run DDL on different schema
I have two schemas on a database named DBO and Switch. I have created this DDL trigger that logs DDL changes on DBO to a table. I then check if the objects affected by the DDL are partitioned or not and if yes I run the same DDL on the other schema…
sdave
- 11
- 2
1
vote
2 answers
How to audit SQL Server schema updates
Is there a way to see who updated which database object and when? I would like to be able to see which developer made a change to some table or stored procedure (or any other object) historically.
I know that we can implement DDL triggers and start…
Kenneth Hampton
- 685
- 6
- 7
1
vote
1 answer
How to prevent only non-empty tables from being dropped using a DDL trigger?
I want to prevent dropping table if there are rows in it.
I wrote:
create trigger prevDrop on database for drop_table
as
begin
if exists (select * from dropped_table)
raiserror('cant do',25,1)
end
But I am getting a syntax error with the…
user1300900
- 11
- 3
1
vote
1 answer
Logging changes made to SQL database
I have an sql database with tables, fields, and indices that is constantly worked on and revised. I would like to be able to log each time any changes are made to the content, including the old version of the specific table/field data that was…
hp43
- 357
- 2
- 5
- 14
1
vote
2 answers
in postgresql, is ti possible to create triggers to a table with triggers on CREATE TABLE?
is is possible in postgres to have a trigger on CREATE TABLE that will create triggers for the newly created table?
for example,
CREATE TABLE base_abc(
...
) inherits( base );
I would like to automatically add triggers to the newly create table…
cc young
- 18,939
- 31
- 90
- 148
0
votes
0 answers
Trigger/Procedure/Audit method to get the module details of all the DML,DCL,DDL performed by "SYS" user on a particular schema
I need help to write a trigger/audit/procedure to get the session details like module, program in for the DML/DDL,DCL performed by "SYS" or "SYSTEM" on a particular schema e.g. HR.
CASE1: This is the audit policy created to fetch all the actions of…
pavankumar2028
- 43
- 4
0
votes
1 answer
DDL trigger to prevent dropping PK
I have a table with a PK
create table a(x int constraint PK_x primary key(x))
How do I make a DDL trigger that prevents the dropping of the PK?
alter table a drop constraint PK_x
I can't get if the constraint is a PK from…
George Menoutis
- 6,894
- 3
- 19
- 43
0
votes
0 answers
SQL Create Trigger On Tables That Are Added By New Users?
Hope someone can guide a noob as I have done alot of googling and searching here as well but here's what I am trying to accomplish.
I have a program that upon registering it creates a table that is yours in the database. A workerID is generated…
Stavros
- 23
- 1
- 5
0
votes
1 answer
Oracle DDL trigger: how to retrieve more details about the event occurred?
I want to make an history on an Oracle DB about all the DDL operations which are executed by the time.
I have created a TABLE and a DDL TRIGGER for do that, in this way:
CREATE TABLE AUDIT_DDL (
D DATE,
OSUSER VARCHAR2(255),
CURRENT_USER…
Alessandro C
- 3,310
- 9
- 46
- 82
0
votes
1 answer
SQL Server 2012 ALL SERVER Trigger issue
I am having an issue with setting an ALL SERVER trigger.
I am using the Adventureworks2012 database to try and get an auditing trigger working.
I would like to ideally have the trigger write to a database that I will make called audit2012 if…
user3008146
0
votes
1 answer
"Update a Trigger" after an alter table command MYSQL
I would like to know if it's possible in MYSQL to "update a trigger" (drop and recreate), after an alter table command?
If yes, how can I do this?
NikolasKraschowetz
- 61
- 3