please pardon my English i am using language translator.
I have a table summary ,i want a TRIGGER to fire before insert on this table i.e summary and what i want is that, the TRIGGER should check if the incoming date matches with existing date, it should update the table and add incoming value to existing value else it should just insert

summary
+------+-------+-------------+------+
| date |income | expenditure | other|
|22/17 | 200   |   50        |  30  |
|22/17 | 100   |   10        |  80  |
|23/17 | 50    |   100       |  0   |
+------+-------+-------------+------+

summary // this is how i want it to be
+------+-------+-------------+------+
| date |income | expenditure | other|
|22/17 | 300   |   150       |  30  |
|23/17 | 50    |   100       |  0   |
+------+-------+-------------+------+

my table code

CREATE TABLE summary (
  id_ int(11) NOT NULL,
  date_ date NOT NULL,
  income_ text NOT NULL,
  expenditure_ text NOT NULL,
  other_ text NOT NULL,
  PRIMARY KEY(id) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO summary 
(id_, date_, income_, expenditure_, other_)
VALUES
(1, '2017-12-22', 200, 50, 30),
(2, '2017-12-22', 100, 10, 80),
(3, '2017-12-23', 50, 100, 0);

This is what i have been trying so far, though am a Novist
trigger code

DELIMITER
$$
CREATE TRIGGER inc_trig 
BEFORE INSERT 
ON income_
FOR EaCH ROW
BEGIN
DECLARE income,expenditure,other INT;

SET income=1; SET expenditure=2; SET other=3;

IF( NEW.date_ != date_ )THEN

IF NEW.name_ = income THEN
INSERT INTO summary (date_,income)
VALUES (new.date_,new.amount);

ELSEIF NEW.name_ = expenditure THEN
INSERT INTO summary (date_,expenditure_)
VALUES (new.date_,new.amount);

ELSEIF NEW.name_ = other THEN
INSERT INTO summary (date_,other_)
VALUES (new.date_,new.amount);


ELSE(new.date_ = date_)THEN

IF NEW.name_ = income_ THEN
UPDATE summary SET income_ = income_ + new.amount);

ELSEIF NEW.name_ = expenditure THEN
UPDATE summary SET expenditure_ = expenditure_ + new.amount);

ELSEIF NEW.name_ = other THEN
UPDATE summary SET other_ = other_ + new.amount);


END IF;

END
$$

I have a database replication setup in MariaDB where the main database is replicated fully to the secondary database, and only a single table is replicated back in the opposite direction. I achieved this by setting both database servers up as masters and slave and applying a table filter on the master i.e. the main database, only allowing operations made on that table. Configuration on primary database server: [mysqld] # REPLICATION server-id=1 log-bin=mysql-bin binlog-do-db=NHMD replicate-do-table=NHMD.spdataset Configuration on secondary database server: [mysqld] # REPLICATION server-id=2 log-bin=mysql-bin binlog-do-db=NHMD So far so good, but while testing this out in our test environment, I discovered, to my shock, that the filter does not apply to DDL operations, meaning -for instance- that if I drop the secondary database, the main database will be dropped as well. This is a highly undesirable situation and poses a grave risk. I have investigated whether the filter could be expanded to DDL operations but alas, it could not. Then, GitHub copilot bizarrely gaslit me into believing that it is possible to set a DROP DATABASE trigger that would prevent this, if I applied it to the secondary database. It was certain that this feature was made available from 10.7.*, but when I looked into it, it appeared to be a mere hallucination. So I am still nowhere with a foolproof solution to this dilemma and the only two things I can think of are: * Disable the two-way replication * Make sure that no one ever by mistake drops or otherwise seriously alters the structure of the secondary database Any other suggestions?

Is there a way to make a DDL trigger which fires on ALTER of *any* object? So far the only way I see to do this is enumerate each object type (e.g. ALTER_TABLE, ALTER_PROCEDURE, etc) when what I'd really like is something like ALTER_ANY.

I have created the following test environment: USE [OmegaCoreAudit] GO /****** Object: Table [dbo].[TEST_TRAIL] Script Date: 6/9/2025 8:02:50 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TEST_TRAIL]( [TIMESTAMP_STS] [datetime2](7) NOT NULL, [APP_01] [nvarchar](200) NULL, [APP_02] [nvarchar](200) NULL, [SQL_TEXT] [nvarchar](2000) NULL, [LOGIN_NAME] [nvarchar](200) NULL, [MODE] [nvarchar](50) NULL ) ON [PRIMARY] GO USE [master] GO /****** Object: DdlTrigger [ALL_SRV_LOG] Script Date: 6/9/2025 7:39:42 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [ALL_SRV_LOG] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @EventData XML; declare @v_app_name_01 nvarchar(128); declare @v_sql_text nvarchar(2000); declare @v_app_name_02 nvarchar(128); SET @EventData = EVENTDATA(); set @v_app_name_01 = @EventData.value('(/EVENT_INSTANCE/ApplicationName)', 'nvarchar(128)'); set @v_sql_text = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)'); set @v_app_name_02 = APP_NAME(); insert into [OmegaCoreAudit].dbo.[TEST_TRAIL] (TIMESTAMP_STS, app_01, app_02, sql_text, login_name, mode) values (CURRENT_TIMESTAMP, @v_app_name_01, @v_app_name_02, @v_sql_text, ORIGINAL_LOGIN(), 'LOG') ; END USE [master] GO /****** Object: DdlTrigger [ALL_SRV_DDL] Script Date: 6/9/2025 7:21:26 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [ALL_SRV_DDL] ON ALL SERVER WITH EXECUTE AS 'sa' FOR DDL_EVENTS AS BEGIN DECLARE @EventData XML; declare @v_app_name_01 nvarchar(128); declare @v_sql_text nvarchar(2000); declare @v_app_name_02 nvarchar(128); SET @EventData = EVENTDATA(); set @v_app_name_01 = @EventData.value('(/EVENT_INSTANCE/ApplicationName)', 'nvarchar(128)'); set @v_sql_text = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)'); set @v_app_name_02 = APP_NAME(); insert into [OmegaCoreAudit].dbo.[TEST_TRAIL] (TIMESTAMP_STS, app_01, app_02, sql_text, login_name, mode) values (CURRENT_TIMESTAMP, @v_app_name_01, @v_app_name_02, @v_sql_text, ORIGINAL_LOGIN(), 'DDL') ; END Left it for a while, and when queried the table, noticed that fields: APP_01 (populated by EventData.ApplicationName) - is always null. APP_02 (populated by APP_NAME()) - is populated. I can understand that for LOGON events the EventData.ApplicationName gives no value, as in the following URL: https://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd ... this is somehow indicated. Question: Why on DDL_EVENT the APP_01 is always empty (while APP_NAME() does give a value)? best regards Altin

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 creates what I want, but when the user creates the database through an application, the trigger throws a permissions error. I've tried splitting the trigger up to call a stored procedure (which has the CREATE USER and ALTER ROLE scripts), then use a certificate signature, but when I run the test with a temp user it errors out at the stored procedure with the message: 'The server principal "tLogin" is not able to access the database "PATest" under the current security context.'. I'm assuming that I haven't given the certificate user the correct permissions, but I'm stuck on how to do that. Has anyone tried to do this before?

Whenever a new table is being created in Schema 'A', I need the table to be duplicated in the Schema 'B'. This should happen automatically. I have searched for trigger concept but it wont work on DDL statements like 'CREATE' or 'ALTER' statements. How do I achieve this without triggers? Please help me.

Got SQL Audit enabled on a database with SQL Server 2016 SP1. Trying to catch a CREATE TABLE event with a trigger, and add that table to the audit to log data operations. Problem is that changes to the state of the db audit can't be made from a trigger or by calling a sproc. Got this: "ALTER AUDIT SPECIFICATION statement cannot be used inside a user transaction." How to get this done ? Please see below how to reproduce my experience so far. --1. create the server audits USE [master] CREATE SERVER AUDIT [Server_Schema] TO FILE ( FILEPATH = N'K:\SQLAudit\RZ_test\' ,MAXSIZE = 1024 MB ,MAX_ROLLOVER_FILES = 10 ,RESERVE_DISK_SPACE = OFF ) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = '00c374df-aaaa-0000-a123-64907441fef5') ALTER SERVER AUDIT [Server_Schema] WITH (STATE = ON) CREATE SERVER AUDIT [Server_DML] TO FILE ( FILEPATH = N'K:\SQLAudit\RZ_test\' ,MAXSIZE = 1024 MB ,MAX_ROLLOVER_FILES = 20 ,RESERVE_DISK_SPACE = OFF ) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = '11c374df-bbbb-4444-a123-12345441fef5') ALTER SERVER AUDIT [Server_DML] WITH (STATE = ON) --2. create a test table USE [testdb] DROP TABLE IF EXISTS [dbo].[RZ_test_t1] IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY] --3. create the db audits within same db --to catch schema changes (such as CREATE TABLE) CREATE DATABASE AUDIT SPECIFICATION [SCHEMA_OBJECT_CHANGE_GROUP] FOR SERVER AUDIT [Server_Schema] ADD (SCHEMA_OBJECT_CHANGE_GROUP) WITH (STATE = ON) --to catch data ops (DEL/INS/UPD) CREATE DATABASE AUDIT SPECIFICATION [DML] FOR SERVER AUDIT [Server_DML] ADD (DELETE ON OBJECT::[dbo].[RZ_test_t1] BY [public]), ADD (INSERT ON OBJECT::[dbo].[RZ_test_t1] BY [public]), ADD (UPDATE ON OBJECT::[dbo].[RZ_test_t1] BY [public]) WITH (STATE = ON) --4. run some data ops on test table INSERT INTO [dbo].[RZ_test_t1] (c1) VALUES (1) INSERT INTO [dbo].[RZ_test_t1] (c1) VALUES (2) UPDATE [dbo].[RZ_test_t1] SET c1 = 0 WHERE c1 = 1 DELETE FROM [dbo].[RZ_test_t1] WHERE c1 = 2 SELECT c1 FROM [dbo].[RZ_test_t1] TRUNCATE TABLE [dbo].[RZ_test_t1] SELECT COUNT(c1) cnt FROM [dbo].[RZ_test_t1] --5. open server audit [Server_DML] log and check ops on step 4 were logged ok, via SSMS GUI --6. drop and recreate table (step 2 again) DROP TABLE IF EXISTS [dbo].[RZ_test_t1] IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY] --7. script out the database audit [DML] and confirm the test table audit part was dropped out of that when table dropped, -- and not added back to audit when table recreated (should look as below). /* CREATE DATABASE AUDIT SPECIFICATION [DML] FOR SERVER AUDIT [Server_DML] WITH (STATE = ON) GO */ --8. run step 4 (data ops) again, nothing will come up in the DML log, as expected. --9. create a trigger in same db, to catch the CREATE for the test table, that should allow for the table to be audited again. CREATE TRIGGER [RZ_trigger1] ON DATABASE AFTER CREATE_TABLE AS BEGIN ALTER DATABASE AUDIT SPECIFICATION [DML] ADD (DELETE ON OBJECT::[dbo].[RZ_test_t1] BY [public]), ADD (INSERT ON OBJECT::[dbo].[RZ_test_t1] BY [public]), ADD (UPDATE ON OBJECT::[dbo].[RZ_test_t1] BY [public]) END GO --10. drop and recreate table (step 2 again) and see the error "Changes to an audit specification must be done while the audit specification is disabled." DROP TABLE IF EXISTS [dbo].[RZ_test_t1] GO IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY] GO --11. change the trigger (required table to exist) and only have it disable the audit for now DROP TRIGGER IF EXISTS [RZ_trigger1] ON DATABASE GO IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY] GO CREATE TRIGGER [RZ_trigger1] ON DATABASE AFTER CREATE_TABLE AS ALTER DATABASE AUDIT SPECIFICATION [DML] WITH (STATE=OFF) GO --12. drop and recreate table (step 2 again) and see another error "ALTER AUDIT SPECIFICATION statement cannot be used inside a user transaction." DROP TABLE IF EXISTS [dbo].[RZ_test_t1] GO IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY] GO --13. create a sproc to disable the audit CREATE OR ALTER PROCEDURE [dbo].[usp_AlterSQLAudit] AS ALTER DATABASE AUDIT SPECIFICATION [DML] WITH (STATE=OFF) GO --14. change the trigger (requires table to exist) to use the sproc DROP TRIGGER IF EXISTS [RZ_trigger1] ON DATABASE GO IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY] GO CREATE TRIGGER [RZ_trigger1] ON DATABASE AFTER CREATE_TABLE AS EXEC [dbo].[usp_AlterSQLAudit] GO --15. drop and recreate table (step 2 again) and see again error "ALTER AUDIT SPECIFICATION statement cannot be used inside a user transaction." DROP TABLE IF EXISTS [dbo].[RZ_test_t1] GO IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY] GO

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, * FROM sys.triggers AS t; Likewise, I can find it in sys.dm_exec_trigger_stats. I can't get object_name to resolve, but object_definition does. SELECT OBJECT_NAME(dets.object_id, dets.database_id) AS object_name, OBJECT_DEFINITION(dets.object_id) AS object_definition, * FROM sys.dm_exec_trigger_stats AS dets; Is there a function that will accept a database level trigger's object id, and return its name?

I have created the 2 After Insert Trigger on same Table. 1st Trigger executed successfully. But 2nd Trigger is not executing and giving the exception from c# code **'Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded.'** Earlier I used the CTE in my trigger but I read that CTE is not supported in Triggers. Then I used Temp Tables. ALTER TRIGGER [dbo].[CodedDatas_INS_ProjectOperationalDataUserWise] ON [LIO00110].[dbo].[CodedDatas] AFTER INSERT AS BEGIN --UPDATE [SydCoding].[DBO].[ProjectReportOperationDataUserWise] -- SET DocCorrected = 0 SET NOCOUNT ON; CREATE TABLE #CTE1 (EnteredBy varchar(50), ReviewedTimeInMin Decimal(30,4),CodingTimeInMin Decimal(30,4)); CREATE TABLE #CTE2 (EnteredBy varchar(50), CodingTimeInHR Decimal(30,4),ReviewedTimeInHR Decimal(30,4)); CREATE TABLE #CTE3 (EnteredBy varchar(50), DocCoded int); CREATE TABLE #CTE4 (EnteredBy varchar(50), DocReviewed int); CREATE TABLE #CTE5 (EnteredBy varchar(50), DocCorrected int); CREATE TABLE #CTE6 (EnteredBy varchar(50), LastModified varchar(50)); CREATE TABLE #CTE7 (ProjectId varchar(50),EnteredBy varchar(50), CODINGRATE Decimal(30,4), REVIEWRATE Decimal(30,4), DocCoded int, DocReviewed int, CodingTimeInMin Decimal(30,4),ReviewedTimeInMin Decimal(30,4),DocCorrected int,LastModified varchar(50),PerDocCorrected Decimal(30,4)); INSERT INTO #CTE1 (EnteredBy,ReviewedTimeInMin,CodingTimeInMin) Select C1.EnteredBy,ReviewedTimeInMin=Sum(ROUND(CAST(C1.QATime AS FLOAT)/(CAST(60 AS FLOAT)),4)), CodingTimeInMin=Sum(ROUND(CAST(C1.CODINGTIME AS FLOAT)/(CAST(60 AS FLOAT)),4)) from Inserted C1 INNER JOIN (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2 on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified group by C1.EnteredBy; INSERT INTO #CTE2 (EnteredBy,CodingTimeInHR,ReviewedTimeInHR) Select CodingTimeInHR=ROUND(CodingTimeInMin/CAST(60 AS FLOAT), 4) , ReviewedTimeInHR=ROUND(ReviewedTimeInMin/CAST(60 AS FLOAT), 4), EnteredBy from #CTE1; INSERT INTO #CTE3 (EnteredBy,DocCoded) Select C1.EnteredBy,DocCoded=Count(C1.Document_Id) from Inserted C1 INNER JOIN (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2 on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified And C1.Coded=1 group by C1.EnteredBy; INSERT INTO #CTE4 (EnteredBy,DocReviewed) Select C1.EnteredBy,DocReviewed=Count(C1.Document_Id) from Inserted C1 INNER JOIN (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2 on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified And C1.Coded=1 AND C1.Revision=1 group by C1.EnteredBy; INSERT INTO #CTE5 (EnteredBy,DocCorrected) Select C1.EnteredBy,DocCorrected=Count(C1.Document_Id) from Inserted C1 INNER JOIN (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2 on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified And C1.IsCorrected=1 group by C1.EnteredBy; INSERT INTO #CTE6 (EnteredBy,LastModified) Select C1.EnteredBy,LastModified=Max(C2.LastModified) from Inserted C1 INNER JOIN (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2 on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified And C1.Coded=1 AND C1.Revision=1 group by C1.EnteredBy; INSERT INTO #CTE7 (ProjectId,EnteredBy,CODINGRATE,REVIEWRATE,DocCoded,DocReviewed,CodingTimeInMin,ReviewedTimeInMin,DocCorrected,LastModified,PerDocCorrected) Select ProjectId='LIO00110', CT3.EnteredBy, CODINGRATE=(CT3.DocCoded/NULLIF(CT2.CODINGTIMEINHR,0)), REVIEWRATE=(CT4.DocReviewed/NULLIF(CT2.ReviewedTimeInHR,0)),CT3.DocCoded,CT4.DocReviewed, CT1.CodingTimeInMin,CT1.ReviewedTimeInMin,CT5.DocCorrected,CT6.LastModified, PerDocCorrected=(Case When CT3.DocCoded>0 Then (Cast( (ROUND(cast(CT5.DocCorrected AS float)/cast(CT3.DocCoded AS float), 4))*100 As int )) Else 0.0000 END ) From #CTE3 CT3 Inner Join #CTE2 CT2 on CT3.EnteredBy=CT2.EnteredBy Inner Join #CTE4 CT4 on CT3.EnteredBy=CT4.EnteredBy Inner Join #CTE1 CT1 on CT3.EnteredBy=CT1.EnteredBy Inner Join #CTE6 CT6 on CT3.EnteredBy=CT6.EnteredBy Full Join #CTE5 CT5 on CT3.EnteredBy=CT5.EnteredBy; Select ProjectId,EnteredBy,CODINGRATE,REVIEWRATE,CodingTimeInMin,ReviewedTimeInMin,DocCoded,DocReviewed ,DocCorrected, PerDocCorrected,LastModified From #CTE7; MERGE INTO [SydCoding].[DBO].[ProjectReportOperationDataUserWise] AS target USING #CTE7 AS source ON target.ProjectId = source.ProjectId AND target.UserId = source.EnteredBy WHEN MATCHED THEN -- Update existing clients UPDATE SET target.[TotalCoded] = source.DocCoded, target.[TotalReviewed] = source.DocReviewed, target.[CodedTime] = source.CodingTimeInMin, target.[ReviewedTime] = source.ReviewedTimeInMin, target.[CodedRate] = source.CODINGRATE, target.[ReviewedRate] = source.REVIEWRATE, target.[DocCorrected] = source.DocCorrected, target.[PercentDocCorrected] = source.PerDocCorrected, target.[LastModifiedOn] = source.LastModified WHEN NOT MATCHED BY target THEN -- Insert new data INSERT ([ProjectId],[TotalCoded], [TotalReviewed], [CodedTime], [ReviewedTime], [CodedRate], [ReviewedRate], [DocCorrected], [PercentDocCorrected], [UserId], [CreatedOn],[LastModifiedOn]) VALUES (source.ProjectId, source.DocCoded, source.DocReviewed, source.CodingTimeInMin,source.ReviewedTimeInMin, source.CODINGRATE, source.REVIEWRATE, source.DocCorrected, source.PerDocCorrected, source.EnteredBy, GetDate(), source.LastModified) ; DROP TABLE #CTE1; DROP TABLE #CTE2; DROP TABLE #CTE3; DROP TABLE #CTE4; DROP TABLE #CTE5; DROP TABLE #CTE6; DROP TABLE #CTE7; End What is wrong in my trigger or I am doing something wrong. Please suggest.

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 .

When I am trying to execute this trigger getting following error:

CREATE OR REPLACE TRIGGER create_table_trigger AFTER CREATE ON SCHEMA DECLARE
    lv_sql     VARCHAR2(5000);
   obj_name   VARCHAR2(500);
BEGIN
    obj_name := sys.dictionary_obj_name;
    lv_sql := 'EXEC SYS.DBMS_FGA.add_policy (object_schema     => ''AIM_DBA'',object_name => '''|| obj_name|| ''',policy_name => ''PROTECT_'|| obj_name || ''',audit_condition  => null,audit_column => NULL,handler_schema => ''AIM_DBA'',handler_module =>''WHERE_CLAUSE_PROTECTOR_PKG.TABLE_PROTECTOR'',enable => TRUE,statement_types => ''UPDATE,DELETE'')';
    IF
        sys.dictionary_obj_type = 'TABLE'
    THEN
        dbms_output.put_line(obj_name);
        dbms_output.put_line(lv_sql);
        EXECUTE IMMEDIATE lv_sql;
        dbms_output.put_line(lv_sql);
    END IF;

END;
/

Execution error Message:

SQL> create table aim_dba.WHERE_TST27
(
    dummy varchar2(20)
);  2    3    4
WHERE_TST27
EXEC SYS.DBMS_FGA.add_policy (object_schema     => 'AIM_DBA',object_name => 'WHERE_TST27',policy_name => 'PROTECT_WHERE_TST27',audit_condition  => null,audit_column => NULL,handler_schema =>
'AIM_DBA',handler_module =>'WHERE_CLAUSE_PROTECTOR_PKG.TABLE_PROTECTOR',enable => TRUE,statement_types => 'UPDATE,DELETE')
create table aim_dba.WHERE_TST27
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'AIM_DBA.CREATE_TABLE_TRIGGER'
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement
ORA-06512: at line 17*

Purpose of Trigger: Whenever new object create Audit policy would get enabled for that object with this 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 pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'caught % event on %', r.command_tag, r.object_identity;
    END LOOP;
 END;
 $$
 LANGUAGE plpgsql;

CREATE EVENT TRIGGER insert_event ON ddl_command_end
  WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE FUNCTION', 'ALTER TABLE', 'DROP TABLE')
 EXECUTE PROCEDURE insert();

create TEMP table my_table(id serial primary key);

This is the output I see:

CREATE TABLE
CREATE FUNCTION
CREATE EVENT TRIGGER
CREATE FUNCTION
CREATE EVENT TRIGGER
NOTICE:  caught CREATE SEQUENCE event on pg_temp.my_table_id_seq
NOTICE:  caught CREATE TABLE event on pg_temp.my_table
NOTICE:  caught CREATE INDEX event on pg_temp.my_table_pkey
NOTICE:  caught ALTER SEQUENCE event on pg_temp.my_table_id_seq

How do I exclude temporary tables from invoking the trigger?

I've created a function in PostgreSQL 11.10 to handle DDL change in pglogical to apply it into subscriber. Here is the function: CREATE OR REPLACE FUNCTION public.intercept_ddl() RETURNS event_trigger LANGUAGE plpgsql AS $function$ declare _qry text; BEGIN if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then SELECT current_query() into _qry; PERFORM pglogical.replicate_ddl_command('_qry', '{default}'), _qry; end if; END; $function$ ; When I tried it, it threw an error: > ERROR: syntax error at or near "_qry" > LINE 1: SELECT pglogical.replicate_ddl_command('_qry', '{default}') > ^ > QUERY: SELECT pglogical.replicate_ddl_command('_qry', '{default}') > CONTEXT: during execution of queued SQL statement: _qry > PL/pgSQL function intercept_ddl() line 6 at PERFORM How to pass _qry variable so we can use it replicate_ddl_command()? --- After fixing single quotes as instructed by Laurenz, I tried again with the command: alter table test alter COLUMN description type text; And got another error message: > ERROR: cannot drop active portal "pglogical" > CONTEXT: during execution of queued SQL statement: alter table test alter COLUMN description type text; SQL statement "SELECT pglogical.replicate_ddl_command( _qry, '{default}')" PL/pgSQL function intercept_ddl() line 7 at PERFORM

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
	CREATE_TABLE,
	ALTER_TABLE,
	DROP_TABLE
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO TableLog (
		EventDate,
		EventType,
		Existing_Table_Name,
		New_Table_Name,
		Changed_By
	)
	VALUES (
		GETDATE(),
		EVENTDATA().value('(/EVENT_INSTANCE/EventType)', 'NVARCHAR(100)'),
		EVENTDATA(),
		EVENTDATA(),
		USER
	);
END;
GO

But for example I change a name of a column in a table the event data commandtext XML shows this

ALTER TABLE dbo.Languages SET (LOCK_ESCALATION = TABLE)

Instead of the full command. How do I stop this from locking and letting me see the full command?

I need to get the object name and user name from oracle schema after fire a trigger. It executes on DDL statements. I need to get executed user name and object name which trigger executed on. Below is my PL/SQL code. CREATE OR REPLACE TRIGGER orders_changes after drop or alter or create on schema DECLARE v_username varchar2(10); obj_name varchar2(300); BEGIN -- Find username and object name SELECT user, object_name INTO v_username, obj_name FROM dual; insert into table1 values (v_username, obj_name); END; Seems issue is with the way I access the object name. It gives an error. Please any one can help me to solve this.

Take the following table as an example: GroupID AlphaID BetaID 1 0 1 1 1 1 1 2 2 2 0 1 2 1 2 2 2 2 2 3 2 3 0 1 3 1 1 I would like to write a check constraint that checks the following: **1. AlphaID - Check Constraint** - The first instance of AlphaID in a GroupID must start at 0 - E.g inserting *GroupID = 4* with an *AlphaID = 0* will succeed - E.g inserting *GroupID = 4* with an *AlphaID = 1* will fail - AlphaID must be unique within each GroupID and must be +1 greater than current max AlphaID - E.g inserting *GroupID = 3* with an *AlphaID = 1* will fail (duplicate) - E.g inserting *GroupID = 3* with an *AlphaID = 2* will succeed (current max + 1) - E.g inserting *GroupID = 3* with an *AlphaID = 4* will fail (out of order) **2. BetaID - Check Constraint** - The first instance of BetaID in a GroupID must start at 1 - E.g inserting *GroupID = 4* with an *BetaID = 0* will fail - E.g inserting *GroupID = 4* with an *BetaID = 1* will succeed - BetaID can have duplicates within GroupID or may be +1 greater than current max BetaID - E.g inserting *GroupID = 3* with an *BetaID = 1* will succeed - E.g inserting *GroupID = 3* with an *BetaID = 2* will succeed - E.g inserting *GroupID = 3* with an *BetaID = 4* will fail (out of order) **A solution that uses a trigger may also be suitable.**

I'm using the below database-level trigger for schema-change-auditing.
It works fine, but if I do (drop view - create view), it only stores the sql that created the view, not the one that is dropped. Say on drop procedure, is there a universal way to save the procedure definition of the old version of the procedure as well ? IF NOT EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Schema_Change_Audit_Trigger') EXECUTE('CREATE TRIGGER [Schema_Change_Audit_Trigger] ON DATABASE FOR CREATE_USER AS BEGIN SELECT 123 AS abc END;'); GO /* IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Schema_Change_Audit_Trigger') DROP TRIGGER Schema_Change_Audit_Trigger ON DATABASE GO */ ALTER TRIGGER Schema_Change_Audit_Trigger ON DATABASE FOR -- https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events?view=sql-server-2017 -- https://stackoverflow.com/questions/1255947/determine-what-user-created-objects-in-sql-server -- https://blog.sqlauthority.com/2015/09/12/sql-server-who-dropped-table-or-database/ -- https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio CREATE_USER, ALTER_USER, DROP_USER ,CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA ,CREATE_TABLE, ALTER_TABLE, DROP_TABLE ,CREATE_VIEW, ALTER_VIEW, DROP_VIEW ,CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE ,CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION ,CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER ,CREATE_TYPE, DROP_TYPE ,CREATE_INDEX, ALTER_INDEX, DROP_INDEX ,CREATE_QUEUE, ALTER_QUEUE, DROP_QUEUE ,RENAME -- https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/ AS BEGIN SET NOCOUNT ON; DECLARE @EventData XML; DECLARE @operation_ip varchar(48); DECLARE @operation_principals AS xml; DECLARE @operation_first_principal AS national character varying(256); SET @EventData = EVENTDATA(); SET @operation_ip = CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address')); SET @operation_principals = ( SELECT lt.name FROM sys.login_token AS lt INNER JOIN sys.server_principals AS sp ON lt.principal_id = sp.principal_id WHERE (1=1) AND lt.name COLLATE Latin1_General_CI_AS NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL) AND lt.name COLLATE Latin1_General_CI_AS NOT IN (SELECT name FROM sys.server_principals WHERE type = 'R' AND name IS NOT NULL ) AND lt.name IS NOT NULL GROUP BY lt.name ORDER BY name FOR XML PATH(''), ROOT ('names') ); SET @operation_first_principal = ( SELECT TOP 1 p.c.value('.', 'nvarchar(256)') AS names FROM @operation_principals.nodes('//name') AS p(c) ); IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'schema_audit' AND TABLE_NAME = 'ddl_events') RETURN; INSERT schema_audit.ddl_events ( audit_event_type ,audit_schema_name ,audit_object_name ,audit_sql_command ,audit_event_xml ,audit_ip_address ,audit_host_name ,audit_app_name ,audit_principal ,audit_principals ,audit_user ,audit_current_user ,audit_session_user ,audit_system_user ,audit_user_name -- same as user ,audit_suser_name ,audit_suser_sname ,audit_original_login ,audit_is_sysadmin ,audit_is_db_owner ,audit_is_ddl_admin ,audit_is_db_datareader ,audit_machine_name ,audit_instance_name ,audit_server_name ,audit_netbios_name ,audit_database_name ,audit_net_transport ,audit_protocol_type ,audit_auth_scheme ,audit_local_net_address ,audit_local_tcp_port ,audit_client_net_address ,audit_physical_net_transport ) SELECT -- Did what ? @EventData.value('(/EVENT_INSTANCE/EventType)', 'NVARCHAR(100)') AS audit_event_type ,@EventData.value('(/EVENT_INSTANCE/SchemaName)', 'NVARCHAR(256)') AS audit_schema_name ,@EventData.value('(/EVENT_INSTANCE/ObjectName)', 'NVARCHAR(256)') AS audit_object_name ,@EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'NVARCHAR(MAX)') AS audit_sql_command ,@EventData AS audit_event_xml -- Where from ? ,@operation_ip AS audit_ip_address ,HOST_NAME() AS audit_host_name ,APP_NAME() AS audit_app_name -- Who did it ? ,@operation_first_principal AS audit_principal ,@operation_principals AS audit_principals ,CAST(USER AS nvarchar(256)) AS audit_user ,CAST(CURRENT_USER AS nvarchar(256)) AS audit_current_user ,CAST(SESSION_USER AS nvarchar(256)) AS audit_session_user ,CAST(SYSTEM_USER AS nvarchar(256)) AS audit_system_user ,CAST(USER_NAME() AS nvarchar(256)) AS audit_user_name -- same as user ,CAST(SUSER_NAME() AS nvarchar(256)) AS audit_suser_name ,CAST(SUSER_SNAME() AS nvarchar(256)) AS audit_suser_sname ,CAST(ORIGINAL_LOGIN() AS nvarchar(256)) AS audit_original_login -- What rights did said person have ? ,IS_SRVROLEMEMBER('sysadmin') AS audit_is_sysadmin ,IS_MEMBER('db_owner') AS audit_is_db_owner ,IS_MEMBER('db_ddladmin') AS audit_is_ddl_admin ,IS_MEMBER('db_datareader') AS audit_is_db_datareader -- On which server was this done ? ,CAST(SERVERPROPERTY(N'MachineName') AS nvarchar(255)) AS audit_machine_name ,CAST(SERVERPROPERTY(N'InstanceName') AS nvarchar(255)) AS audit_instance_name ,CAST(SERVERPROPERTY(N'ServerName') AS nvarchar(255)) AS audit_server_name ,CAST(SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS nvarchar(255)) AS audit_netbios_name ,DB_NAME() AS audit_database_name -- Connection information ,CAST(ConnectionProperty('net_transport') AS nvarchar(255)) AS audit_net_transport ,CAST(ConnectionProperty('protocol_type') AS nvarchar(255)) AS audit_protocol_type ,CAST(ConnectionProperty('auth_scheme') AS nvarchar(255)) AS audit_auth_scheme ,CAST(ConnectionProperty('local_net_address') AS nvarchar(255)) AS audit_local_net_address ,CAST(ConnectionProperty('local_tcp_port') AS nvarchar(255)) AS audit_local_tcp_port ,CAST(ConnectionProperty('client_net_address') AS nvarchar(255)) AS audit_client_net_address ,CAST(ConnectionProperty('physical_net_transport') AS nvarchar(255)) AS audit_physical_net_transport WHERE (1=1) -- every morning, index compression of all indices at 05:15 -- we don't want to to log that AND ( @EventData.value('(/EVENT_INSTANCE/EventType)', 'NVARCHAR(100)') IS NULL OR @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'NVARCHAR(MAX)') IS NULL OR NOT ( @EventData.value('(/EVENT_INSTANCE/EventType)', 'NVARCHAR(100)') = 'ALTER_INDEX' AND ( @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'NVARCHAR(MAX)') LIKE '%REORGANIZE%' AND @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'NVARCHAR(MAX)') LIKE '%LOB_COMPACTION%' ) ) ); END GO Log table: IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'schema_audit' ) EXECUTE('CREATE SCHEMA schema_audit; '); GO -- IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'schema_audit' ) EXECUTE('DROP TABLE schema_audit.ddl_events; '); IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'schema_audit' AND TABLE_NAME = 'ddl_events' ) BEGIN CREATE TABLE schema_audit.ddl_events ( audit_uid uniqueidentifier NOT NULL DEFAULT NEWID() ,audit_event_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,audit_event_type national character varying(64) ,audit_schema_name national character varying(256) ,audit_object_name national character varying(256) ,audit_sql_command national character varying(MAX) ,audit_event_xml xml ,audit_ip_address character varying(48) ,audit_host_name national character varying(256) ,audit_app_name national character varying(256) ,audit_principal national character varying(256) ,audit_principals xml ,audit_user national character varying(256) ,audit_current_user national character varying(256) ,audit_session_user national character varying(256) ,audit_system_user national character varying(256) ,audit_user_name national character varying(256) -- same user ,audit_suser_name national character varying(256) ,audit_suser_sname national character varying(256) ,audit_original_login national character varying(256) ,audit_is_sysadmin int ,audit_is_db_owner int ,audit_is_ddl_admin int ,audit_is_db_datareader int ,audit_machine_name national character varying(256) ,audit_instance_name national character varying(256) ,audit_server_name national character varying(256) ,audit_netbios_name national character varying(256) ,audit_database_name national character varying(256) ,audit_net_transport national character varying(256) ,audit_protocol_type national character varying(256) ,audit_auth_scheme national character varying(256) ,audit_local_net_address national character varying(256) ,audit_local_tcp_port national character varying(256) ,audit_client_net_address national character varying(256) ,audit_physical_net_transport national character varying(256) ,CONSTRAINT PK_ddl_events PRIMARY KEY( audit_uid ) ); END

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 XML = EVENTDATA()
	   DECLARE @TableName NVARCHAR(50) 
               = @EventData.value('(/EVENT_INSTANCE/ObjectName)', 'NVARCHAR(50)')
	   DECLARE @Database NVARCHAR(50) = DB_NAME()

       PRINT @Database
	   PRINT @TableName
END
GO

If I create a table in database ABC123 it prints: > master
TestTable

I'm using Postgres 11.5 and have been checking out event_trigger. Amazing. For the uninitiated, event triggers are like table triggers, except for (most) DDL operations. So, CREATE FUNCTION, ALTER FUNCTION, DROP TABLE, and much more. Given that DDL operations are transactional in Postgres, I guess is was manageable to add global traps for these operations. With an event trigger, you can capture and respond to various DDL events, with limits. So, pretty interesting if, for example, you're trying to track changes to some part of the catalog. There's a built-in system view named pg_event_trigger that shows what's defined. Like most things pg_catalog, the columns have short, dense names and list the old values of reference objects in other catalog tables. I tend to create my own views for this sort of thing and stash them in a schema named dba. In case it saves someone else a few minutes down the track, here's one version for event triggers: select evtname as event_trigger_name, evtevent as event_name, evtowner::regrole as event_owner, evtfoid::regproc as function_name, case when evtenabled = 'A' then 'Always' when evtenabled = 'D' then 'Disabled' when evtenabled = 'O' then 'Origin and Local' when evtenabled = 'R' then 'Replica' end as session_replication_role_mode, coalesce(evttags::text,'No TAG filter') as event_tags from pg_event_trigger; That's just the query part of what goes into the view.

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() -> RETURNS event_trigger -> LANGUAGE plpgsql -> AS $$ $> BEGIN $> SELECT 1; $> END; $> $$; CREATE FUNCTION => CREATE EVENT TRIGGER do_nothing_on_ddl_change ON ddl_command_start -> EXECUTE PROCEDURE do_nothing(); ERROR: permission denied to create event trigger "do_nothing_on_ddl_change" HINT: Must be superuser to create an event trigger. The root reason for needing this is that I'm using an RDS-backed Postgres instance, and attempting to setup streaming of that database (data and schema changes) into a Redshift instance. Using Amazons DMS services, setting this up is up is fairly straightforward - however, there's a catch: Starting up the streaming process creates a DDL audit table in the database, as well as an event trigger that captures any DDL changes. The trigger writes the changes to the audit table, and those changes are streamed into redshift. Here's where the problems lie: * A non-superuser (lets call it app_production, since this is part of a rails app) is responsible for making DDL changes to the db. * If we use the non-superuser to create the data streaming task in AWS, that user is unable to create the required event triggers to stream schema changes. * If we use the superuser to create the streaming task, then future schema migrations made by app_production fail, as they attempt to write to an audit table created by the superuser. Any ideas?