New answers tagged

0

Why write a trigger for this ? I have two reasons why you should not do that in this instance. REASON #1 Let's assume the following: reference_key is not an auto_increment column is the primary key counter is the column to increment is defined as INT NOT NULL DEFAULT 1 You can write the INSERT as follows INSERT INTO individual_key_log ...


-1

I have spent a few days to come up with a Stored Procedure to automatically/dynamically create UPDATE / DELETE triggers in MariaDB (Works with v 10.1.9) auditing all changes on updates and deletions. The solution uses the INFORMATION_SCHEMA to automatically build an audit trigger for each of your tables. On Update only changed columns are audited, whilst on ...


1

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key. In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


0

as the number of records increase in TABLE_B, trigger performance degrades rapidly That would imply the updates do not have appropriate indexes available, or are unable to use them for some reason, on TABLE_B or the tables that it is joined to. Without more detail about the table layout (what indexes you have on each of the tables) and the query plan ...


0

as the number of records increase in TABLE_B, trigger performance degrades rapidly. Which indicates the update statements accessing TABLE_B are inefficient - because of missing indices. Standard debugging will help.


1

You can use this format for copying your original values over: CREATE OR REPLACE TRIGGER update_Trigger BEFORE UPDATE ON customer FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO replica VALUES(:OLD.cID, :OLD.cName, ... ); END IF; END; / You will need to specify each field you want inserted into replica This will be slow and cause a lot ...


1

Have you considered using a trace for that? https://technet.microsoft.com/en-us/library/ms191006%28v=sql.105%29.aspx You would be able to configure it for specific tables and store output in a table. I also have to point out that, with the method you are trying to use, you will only be able to capture the query used to change the table. In some situations ...


1

I do not think that the check is needed on delete. So create a trigger like: CREATE OR REPLACE TRIGGER check_session_timing BEFORE INSERT OR UPDATE ON sessions FOR EACH ROW BEGIN if :new.SessionDay = 'Saturday' or :new.SessionDay = 'Sunday' then raise_application_error(-20001, 'Out of business hours – this transaction has not ...


0

I was not able to solve this issue in the method I had wanted. I used an if statement as suggested by Serg. if ((:NEW.ONSALE = 'T') AND (:OLD.ONSALE = 'F')) then :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT1; elsif ((:NEW.ONSALE = 'T') and (:OLD.ONSALE = 'T')) then :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT2; end if; This was ...


-2

Maybe you can create DELETE after INSERT. So, if like you case, you can use this trigger. CREATE TRIGGER `delete_after_insert` AFTER INSERT ON `outpatient` FOR EACH ROW BEGIN DELETE FROM `resident` WHERE `residentID` = '' END; You don't need old values for insert to outpatient. because you insert first and get the id for delete on resident ...


1

You should take the vbalues of OLD and use them in INSERT command. Something like this: CREATE TRIGGER triger_name_after_delete AFTER DELETE ON resident FOR EACH ROW BEGIN -- Insert record into Outpatient table INSERT INTO Outpatient (residentID, [rest columns names...] ) VALUES ( OLD.residentID, OLD.[rest columns names...]) END;


1

Since my comment went unanswered, I have converted it to an answer. I want to do some work if it's an insert and some other work if it is a delete. You could create separate triggers, one for insert, and one for delete: CREATE TRIGGER dbo.Table1_AI ON dbo.Table1 AFTER INSERT AS ... insert-specific actions CREATE TRIGGER dbo.Table1_AD ON dbo.Table1 ...


3

TRUNCATE does not fire ON DELETE triggers in any 5.x version. But it does have to lock the table and have to wait for any other open transactions that hold locks on the table to release them. That's what the process list is showing. There is a (transaction with a) delete query that is running and has some lock on the table and is blocking the TRUNCATE ...


1

The trigger always "runs" -- but just because it runs doesn't mean it has to perform any action. And a trigger that doesn't perform any action doesn't take very long, if you're concerned about efficiency. In your case, all you need is logic to compare NEW.SellingPrice to OLD.SellingPrice and only perform the INSERT action if the two values are not the ...


2

If I write it out like this @OldValue = (select deleted.ID from #del) I get the correct value. You only get the correct value if there is a single row being updated or deleted. But Triggers in SQL Server are fired once per DML operation, and the inserted and deleted pseudo-tables contain all of the rows per that operation. So if there were 2 or more ...


7

As long as the trigger is only for insert/delete: IF EXISTS (SELECT 1 FROM inserted) -- I am an insert ELSE -- I am a delete And in fact if you are doing things based on a join to inserted/deleted, it can be even simpler: -- do something for an insert -- this will only actually do anything when it's an insert INSERT dbo.somewhereElse SELECT ...


1

Yes, you can do this inside a trigger. A trigger can reference the objects INSERTED and DELETED which can be used as tables to tell what information is inserted and deleted. When an update is made, the old rows are "deleted" and the new rows are "inserted." Write your SQL statement to INSERT all the rows from INSERTED and DELETED into your log table. I ...


0

Final code: (It works) ALTER TABLE public.companies ADD COLUMN client_code_increment integer; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAULT 1000; COMMIT TRANSACTION; BEGIN; -- Creating the function CREATE OR REPLACE FUNCTION users_code_seq() ...


0

I'm short on time so I wasn't able review your code, but I'm basing this answer off your question: How to get the deleted values/capturing changes? Just yesterday I was reading from CH. 8 of Itzik Ben-Gan's "Microsoft SQL Server 2012 T-SQL Fundamentals" and he had a good section on creating audit tables with the use of the OUTPUT clause. To me this was eye ...


1

try this - you might get the best of both select manyfield from ( select manyfield , row_number() over (partition by t1.id order by t1.timestamp desc) as rownum from inserted left join sometable t1 on inserted.id = t1.id ) tt where rownum = 1 or t1.id is null


0

I think you want to change your table structure. Discounts and items are related but are not always the same. Today you discount T-Shirts by 15% tomorrow you realize you have way too many and want to change that to 20%. Sure you can update the table but then you loose all records of what the discount was. When a customer calls to say they only got a 15% ...


0

In SQL Server Mgmt Studio, first click on the database name that will contain your trigger, then click on New Query and type in your T-SQL w/o any schema info. OR Use the following line of code before your CREATE TRIGGER batch of commands to be executed: USE <databasename> And in case cross-database is needed, you need to be using the current ...


4

The Question mentions that the "code is failing" but there is no indication of any error message or what specifically is failing. Including at least one, if not both, of those pieces of information always helps get better answers. For the moment, I see something that appears to be an incorrect assumption about Triggers and Transactions: you increment the ...


3

A trigger is already always operating within an implicit transaction, see the related question: Is there a way to ensure that a SQL Server trigger will be executed? You can catch an error and prevent an abort/rollback, but you cannot "commit" the transaction from within the trigger.


1

In SQL, triggers can be fired "per row" or "per statement". SQLite only supports "per row" triggers (hence why your trigger fires once for each row in your INSERT statement), while SQL Server only supports "per statement" triggers (hence why your statement causes a single firing). Other engines (e.g. DB2 and PostgreSQL) support both sorts of trigger which ...



Top 50 recent answers are included