Questions tagged [ddl-trigger]
35 questions
11
votes
2 answers
How can I resolve a database trigger's name with built-in functions?
I have a database trigger that I use to prevent me from creating certain procedures in user databases.
It appears in sys.triggers, with an object_id, but I can't use the object_id function to find it.
SELECT OBJECT_ID(t.name, t.type) AS object_id,…
Erik Reasonable Rates Darling
- 45,549
- 14
- 145
- 532
6
votes
1 answer
PostgreSQL event trigger to force extension installations into particular schema
I have a dedicated schema extensions (PostgreSQL v 9.6.3) where I intend to install all extensions for my DB. I would like to somehow disallow (or at least remind) users installing extensions without specify particular schema.
So this should…
zam6ak
- 481
- 1
- 4
- 13
4
votes
1 answer
Create event trigger as non-superuser in Postgres
Is it possible to give a role or a set of attributes to a non-superuser in postgres that would allow the user to create an EVENT TRIGGER? Simple example of what (obviously) doesn't work in psql:
=> CREATE OR REPLACE FUNCTION do_nothing()
-> …
matt
- 141
- 1
- 2
3
votes
2 answers
DDL Trigger in MySQL?
Are there any options for running DDL triggers in a MySQL database. I would like to create a view every time someone create a table.
If the "Users" table is created, I want the DDL trigger to create a view called "v_Users".
eriksv88
- 133
- 1
- 6
3
votes
1 answer
DDL Trigger not working as expected SQL Server 2012
I have a DDL Trigger in SQL Server 2012 as:
CREATE TRIGGER [AuditProcChanges]
ON DATABASE FOR
CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
DECLARE @message_body XML
SET @message_body = EVENTDATA()
BEGIN
BEGIN TRANSACTION
INSERT INTO…
aaru11
- 31
- 1
2
votes
2 answers
Need to add user and assign roles automatically, using DDL Trigger, when database is created
I am trying to create an automated process that will add a user and assign them the roles of db_datareader, db_datawriter and db_ddladmin whenever a database starting with PA is created. I have created a trigger that I can run that perfectly…
Eric
- 21
- 2
2
votes
1 answer
Ignoring temp table in Postgres event trigger
I am trying to have a trigger that gets invoked when new tables, except temporary tables, are created.
This is what I have tried:
CREATE OR REPLACE FUNCTION insert()
RETURNS event_trigger
AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM…
user
- 131
- 1
- 1
- 5
2
votes
1 answer
How to Deny permissions for newly created user in DDL Trigger?
I'm having trouble with a SQL DDL trigger for Create_User and I'm wondering if there is an issue with my syntax or something else I'm not seeing?
I have some tables in my database which are only available to a few users. The rest of the users in the…
Eric MPC
- 108
- 6
1
vote
2 answers
Disable all triggers in a database
How do I disable all triggers in a database from another database which are currently enabled .And then enable it back only the ones I disabled .
Subin Benny
- 77
- 1
- 9
1
vote
2 answers
lock escalation issue when using DDL trigger
I have a problem, I am trying to create a log table for my database so I can keep track of changes. I have created the following trigger that runs when a table is altered, created and dropped:
CREATE TRIGGER TableTrigger
ON DATABASE
FOR
…
Oliver Smith
- 11
- 1
1
vote
1 answer
How to get database name in a server-level DDL trigger
I'm creating a Server level trigger that fires after a create table statement, and I want to print the database name and table name.
CREATE TRIGGER LogTempTables
ON ALL SERVER
AFTER CREATE_TABLE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData…
Matthew
- 43
- 5
1
vote
2 answers
Trigger to update only if value is in lookup table
I only want to update the comment if a description for the event is found in the TagDescLookup Table. If the Tag isn’t in the Lookup table then don’t change the existing Value for the comment column. The current trigger will update the comment…
DAG
- 11
- 2
1
vote
0 answers
Create a table via an INSERT trigger in MariaDB
I'm trying to make a small database I can use to log changes I make to configuration files on various Linux systems.
My thought was have a Database named for the system, and then a table called ConfigFiles that has an ID column, a FileName column…
FreelancerJ
- 111
- 1
1
vote
1 answer
Does the SQL Standard mention trigger events on DDL (ALTER, CREATE, DROP)?
PostgreSQL supports "Event Triggers" which is defined as,
To supplement the trigger mechanism discussed in Chapter 39, PostgreSQL also provides event triggers. Unlike regular triggers, which are attached to a single table and capture only DML…
Evan Carroll
- 65,432
- 50
- 254
- 507
1
vote
1 answer
failed logon trigger blocking access into SQL Server instance
I wanted to create a trigger to record all the names of logins(who logon into system) in ServerLogonRecords table(all the columns are nullable) using LOGON trigger. I get information using eventdata() system function and convert the result into…
igelr
- 2,162
- 3
- 26
- 56