Tag Info

New answers tagged

2

I found this: SOURCE There is great reason why you should never call stored procedures from within triggers. Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive ...


1

I think you have the trigger syntax wrong. Try something like (I don't fully understand the scenario so you will have to adjust the logic in the trigger): delimiter @ create trigger resend after insert on t1 for each row IF NEW.status = 'ERROR' THEN INSERT INTO t2 (time,sender,receiver,status,aux_id) VALUES ...


1

Based on the suggestion I gave in the chat room, here is my answer CREATE TABLE cash_op LIKE cash_operation; Goto the datadir in your Windows server rename cash_op.MYD cash_op.MYX copy cash_operation.MYD cash_op.MYD login to mysql and run REPAIR TABLE cash_op; ALTER TABLE cash_operation RENAME cash_op_old; ALTER TABLE cash_op RENAME cash_operation; Go ...


1

It may probably no longer be a problem, but in the code it seems you forgot that the assignment operator is :=, not =, so typeEngine would be null and the query returns no rows.


0

That Trigger worked. CREATE TRIGGER trgCustomer ON customer INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON DELETE FROM customer WHERE customer.ID IN (Select ID from deleted where name != 'Peter') END;


4

You have two options: 1) Rollback the DELETE in case you're deleting the row "Peter". You do this with an AFTER trigger. Be aware that it will rollback the whole transaction in case yuo're deleting multiple rows among which at least one is "Peter". CREATE TRIGGER TR_CUSTOMER_DEL ON customer FOR DELETE AS BEGIN SET NOCOUNT ON; IF EXISTS ( ...


2

My proposal would be this one: CREATE TABLE UNIQUE_PHYSICIAN ( PARENT_PHYS_ID NUMBER, PHYS_ID NUMBER CONSTRAINT PHYSICIAN_PK PRIMARY KEY); ALTER TABLE DPISYS.UNIQUE_PHYSICIAN ADD CONSTRAINT UNIQUE_PHYSICIAN_PARENT_FK FOREIGN KEY (PARENT_PHYS_ID) REFERENCES DPISYS.UNIQUE_PHYSICIAN (PHYS_ID); CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG ...


0

If I understand your question correctly, if the trigger on YourTable inserts a row into YourTable as well, then that insertion will fire another instance of the same trigger. See previous answers at: http://stackoverflow.com/questions/16391081/how-to-fix-oracle-trigger-causing-recursion-issue Recursion has issues. Yes, I have written recursive code in the ...


0

Yes, if this was to work, it would generate an infinite loop. But more than likely, it will throw a mutating table exception first. What is the problem that you are trying to solve? A trigger that updates other rows in a table when one row changes is unlikely to be a reasonable solution. If you have cross-row dependencies, that almost always indicates ...


7

Trying the following in SSMS.. CREATE TABLE [dbo].[T] ( X INT ); GO CREATE TRIGGER [dbo].[TR] ON [dbo].[T] FOR INSERT AS SELECT * FROM [dbo].[T] Then renaming the table as T2 and scripting out the trigger returns the following so I assume you are referring to the second table reference. CREATE TRIGGER [dbo].[TR] ON [dbo].[T2] FOR ...


1

Try this CREATE TRIGGER Trigger_t1 ON T1 FOR INSERT AS BEGIN SET NOCOUNT ON; insert into T2(C1,C2,C3 ) select C1,C2,C3 from inserted insert into T3(C1,C2,C3 ) select C1,C2,C3 from inserted insert into T4(C1,C2,C3 ) select C1,C2,C3 from inserted END GO DML trigger statements ...


1

(Obvious error in the trigger logic aside.) In Postgres 9.2 or later, use the function pg_trigger_depth() that @Akasha already mentioned in a condition on the trigger itself (instead of the body of the trigger function), so that the trigger function is not even executed when called from another trigger (including itself - so also preventing loops). This ...


1

For SQL Server, a quick and dirty approach that we have used for a number of years without any problems is to use the existence of a temporary table that has a specific name (typically based on the trigger name) in order to provide an easy manual override that bypasses the trigger. The #temp table will be specific to this connection, but remains in scope in ...


3

If you dont want the update trigger to be executed when the its called from within the insert trigger, you can surround your statements with a condition of pg_trigger_depth() which returns the depth, which wont be 0 when you are running the trigger directly/indirectly from another trigger. So, within your function gener_history(), you can do something like ...


1

I answered a question like this back on Jan 20, 2012 : Disable trigger for just one table It was a workaround for MySQL I suggested. No negative feedback so far.


1

SUGGESTION #1 Remove the AFTER INSERT trigger and call add_info from your app SUGGESTION #2 Change the AFTER INSERT trigger into BEFORE INSERT


1

Insert into Employee_History Values(@EmployeeID,@Employeename,@SSN,@Phonenumber,@JoinDate,@OperationHistory,getdate()) Make sure the column order of the Employee_History table matches the order of the variables listed. The best practice is to list column names on INSERT statements. The other possible cause is that the EmployeeName column in the ...


0

If you want to filter on the tablle name if you have too many triggers just add a filter on relname from pg_class SELECT tgrelid::regclass,t.tgname FROM pg_trigger t, pg_proc p, pg_class c WHERE c.oid = t.tgrelid AND t.tgfoid=p.oid AND c.relkind = 'r' AND p.prosrc LIKE '%studentid%' AND c.relname = 'X';


0

You can do that by querying pg_proc and pg_trigger, SELECT t.tgname FROM pg_trigger t, pg_proc p WHERE t.tgfoid=p.oid AND p.prosrc like '%studentid%';


0

You should avoid using triggers in conjunction with REPLACE INTO on multiple rows. Why ? REPLACE INTO is nothing more than a mechanical DELETE and INSERT. It can incite mysqld to address deadlocks (See my answer to How I prevent deadlock occurrence in my application?) Here are two comments that shows the LIFO approach to processing triggers Posted by ...


0

That looks like it should work. I would run one SELECT to retrieve both values SUGGESTION #1 delimiter | CREATE TRIGGER trigger BEFORE INSERT ON table1 FOR EACH ROW BEGIN select value,value2 INTO NEW.value,NEW.value2 from table2 where id=NEW.id; END; | delimiter ; SUGGESTION #2 If the value and value2 are of datatype INT, do the ...



Top 50 recent answers are included