Tag Info

Hot answers tagged

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 ...


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 ( ...


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 ...


3

Dynamic fields are notoriously difficult in plpgsql. In particular there's no way we can write new.variable := something where variable stands for a column name. See How to set value of composite variable field using dynamic SQL for ways that involve querying the catalog at runtime. Personally, I'd suggest a simpler solution with the plv8 language. CREATE ...


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 ...


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 ...


2

I put in a little time to try and develop an answer for this question which may fit your needs, but since I don't have detailed criteria, it may not be perfect. Hopefully, though, it is close enough so that you can manipulate to meet your design needs. Initial assumptions To begin, I had to make a few initial assumptions to design the algorithm. 1) When ...


1

Try this form: EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW; It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays. An even more modern and cleaner version (quote the table's name if necessary): EXECUTE format('insert into %I values ($1.*)', child) USING NEW;


1

Try with: USE `veeva_new`; DELIMITER $$ DROP TRIGGER IF EXISTS veeva_new.userterritory_ai$$ USE `veeva_new`$$ CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW BEGIN # [ VARIABLES DECLARATION ] DECLARE vUserID VARCHAR(18) DEFAULT ''; DECLARE vTerritoriRep VARCHAR(45) DEFAULT ...


1

Your trigger tries to do something that cannot be done in MySQL. You cannot use an SQL statement (DELETE, in your case) on the table that is associated to the trigger. You will get an error like this: ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. ...


1

Good answer from Rolando. In addition -- Triggers should not be used for logic, because a couple of inter-relating triggers and things get confusing very fast. A nice set of instructions can get across the business logic than a bunch of hidden logic in the database. There are also limitations on triggers with respect to the table they get triggered from ...


1

This error means you already have an BEFORE INSERT trigger on companies table. If it is the same trigger (meaning trig_companies_table_insert) that you created earlier and now you want to replace it then you need to drop it first: DROP TRIGGER trig_companies_table_insert; DELIMITER $$ CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON ...


1

It means you already have a trigger for BEFORE INSERT ON companies. (action time = BEFORE, event = INSERT) You can't have two of them for the same event and action time. You can fix it by editing the existing BEFORE INSERT trigger.


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.


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 ...


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 ...



Only top voted, non community-wiki answers of a minimum length are eligible