Tag Info

New answers tagged

1

Use a before update trigger. Do you want to raise an exception or just have it silently not change the value? Yes, you should return NEW. Yes, you should prefix the column name with NEW or OLD. You can use this if you don't want to raise an exception: create function check_id_change() returns trigger language plpgsql as $$ begin new.original_id = ...


0

Unless you create a different database user for each application user -so it is available with the user() or current_user() functions: mysql> SELECT user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> SELECT current_user(); +----------------+ | current_user() | ...


0

One idea would be to write: CREATE TRIGGER `after_insert_log_competency` AFTER INSERT ON `competencies` FOR EACH ROW INSERT INTO history_competencies SELECT *, 'CREATE' FROM competencies WHERE id=NEW.id; That doesn't work well for deletions, as the row wouldn't exist anymore. Also, you are doing an extra eq_ref for each write. But be careful, ...


0

It's hard to give you an exact answer without knowing what RDBMS you are using or what your table schema looks like. But here is an example for SQL Server. CREATE TRIGGER dbo.TR_MYTABLE_UPDATESTAMP ON dbo.[MYTABLE] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; UPDATE I SET I.[MyTableUpdateDate] = GETDATE(), I.[MyTableUpdateBy] = SYSTEM_USER ...


0

In this case, it turns out that the problem was a misdiagnosis of symptoms. The debugger was simply failing to function for some as-yet unknown reason and the trigger was failing to do anything because it was properly executing its code. The conditional before the INSERT statement was missing a check to ensure it was only looking at the current user's ...


0

My initial guess would be based on the trigger's flow. Why ? According to the Book Chapter 11 Page 251 Paragraph 3 says the following: The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and ...


1

Triggers in SQL Server are fired once per set, not per row (like Oracle), so you need to build triggers that handle this. In all triggers, you have two special tables called inserted and deleted, that contain a virtual "mirror" of the entire table row(s) after and before the action that triggered it, respectively. A word of warning: triggers should be ...


2

Because update trigger is invoked once per row being updated, it is not invoked at all if no rows are going to be updated. See for yourself: create table test(i int); create view test_view as select i from test; CREATE OR REPLACE FUNCTION test_trigger_function() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'hi'; RETURN NEW; END; $$ ...


1

Based on CL's suggestion to take another look at last_insert_rowid() and now that it's clear that INSTEAD OF triggers are only for VIEWs (see question's comments), here's a working version: -- We need a view to create the trigger on CREATE VIEW Container_Auto AS select * from Container; -- Since it's automatic we need to check if we need to create a root ...


0

Perhaps you can use some user defined variables. I once wrote an answer to this : Disable trigger for just one table. I suggested setting up a user defined variable to disable a trigger. In your case, dynamic SQL is not necessary if you are simply changing parameter values for a SQL statement. If you are changing table names and column names for the query, ...


1

With regard to SUPER and TRIGGER privileges, you need the TRIGGER privilege to create triggers on tables you have rights for. If you play binlogs, there is the possibility of seeing commands to create triggers on tables you do not have rights for. This is implied from the MySQL Documentation: CREATE TRIGGER requires the TRIGGER privilege for the table ...


2

Why is t_history.id auto-incremented in the first place? If "both t and t_history have the same schema", and t.id is a serial PK, you can just copy whole rows. I would also suggest you only copy rows you actually delete from t to t_history - in a data-modifying CTE. This way you do not have overlapping rows (which might be part of the problem). CREATE ...


2

A partial answer to your question, could be to use DBCC INPUTBUFFER, this doesn't necessarily give you the stored procedure that is deleting your records. It can give you the first stored procedure called by the client, ie: application code calls: EXEC EntryStoredProc /* this call is the one that will be identified by DBCC INPUTBUFFER */ ... /* inside ...


3

Couple of Options: Option1: Your best option is to run a server side trace with filtering options enabled. This way you can narrow down the amount of data captured. Option2: Depending on the version (Enterprise), you can use SQL Audit as it allows you to track DELETE, EXECUTE and other Actions on the Object level as well. So you can filter the table ...


3

something like below should get you started ... CREATE TABLE [dbo].[patientInfo] ( id int, patientid int, starttime datetime, endtime datetime, doctorid int ) GO ALTER TABLE patientInfo ADD expire AS CASE WHEN endtime < GETDATE()THEN 1 ELSE 0 END GO declare @now datetime=getdate() INSERT into patientInfo (id, patientid, starttime, ...


0

you are almost on the right track with a slight misunderstanding. But I have a lot of tables and I don't one to create a trigger on them one by one. This is not correct. You just have to create SERVER LEVEL / DATABASE LEVEL TRIGGER that will take care of the database events that occur on the server instance or database. You can even filter out the ...


1

The actions you're performing on the data is technically different between the insert/update portions, but what about the business logic difference? IMO, your best bet is to contain a functional unit of work inside of a trigger, so that there is no discrepancy in what that trigger does. In my experience, Developers/Business Analysts often tend to think in ...


0

You need to use the Delimiter /// first if you are creating a trigger. Delimiter /// CREATE TRIGGER Section_Insert AFTER INSERT ON Section FOR EACH ROW BEGIN INSERT INTO Audit(changeTime, tableName, Action) VALUES (NOW(), 'Section', 'INSERT'); End; ///



Top 50 recent answers are included