New answers tagged

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


0

Federated tables are not transactional by nature, so you don't want to put a trigger on it. Alternative would be to put a trigger to post to another local table. See: Federated tables and triggers


0

Unfortunately IIRC mySQL does not support INSTEAD OF triggers which is how this could be done in other databases, and you can't cancel the action in a BEFORE UPDATE trigger without cancelling out the whole operation (by raising an error). Even where INSTEAD OF triggers are supported I would generally suggest erring in the direction of Rick's suggestion and ...


2

Dynamically creating multiple triggers for individual rows sounds quite nasty. This is not likely to scale well. After reading your update I would suggest you implement 1 trigger that fires for all CRUD operations on the SELLS table. This trigger checks to see if the modified rows of SELLS are in the SUBSCRIPTION table. If they are send a notification, if ...


3

Triggers run, by default, under the security context of the principal who caused the trigger to fire. In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause. Below is an example which shows how that works. WITH EXECUTE AS OWNER allows the trigger to run in the security context of the database owner, ...


5

If you're happy using EXECUTE AS (trusting users that can impersonate), an alternative is: Tables CREATE TABLE dbo.Test ( TestID integer IDENTITY PRIMARY KEY, SomeDate datetime NOT NULL ); GO CREATE TABLE dbo.TestArchive ( TestID integer PRIMARY KEY, SomeDate datetime NOT NULL ); Users -- Ordinary user with the ability to insert to the ...


7

This can be accomplished using a Certificate and module signing (i.e. ADD SIGNATURE). Using Impersonation via EXECUTE AS can get messy, and it leaves the possibility of someone else Impersonating the "allowed" User, or changing the contents of a module that is using the EXECUTE AS. But with module signing: the Certificate-based User cannot be Impersonated ...


0

Well thanks everyone, but I read somewhere saying that i should include this in the table i want to sync to(to make the table unique) "alter table "YOURTABLE add unique index(row1, row2);" then i add ON DUPLICATE KEY UPDATE name = name and number = number; Here is a final trigger after making the table unique to the one am synchronize to, in this case ...


1

The immediate issue with your first statement is that it has BEGIN without a corresponding END. In this case, END should be at the very end of the CREATE TRIGGER statement. Once you add the END keyword, the statement becomes technically correct, but you will have trouble executing it in MySQL. The reason is that semicolons, while being standard statement ...


1

In PostgreSQL you have to create a trigger function that will perform the calculations and the use it in a trigger. Example: Create or replace function CalculQStockProduit() returns trigger as $$ begin; update QuantiteEnStock set NEW.QuantiteEnStock = NEW.QuantiteEnStock-NEW.Quantite; return NEW; end; $$ language 'plpgsql'; Create trigger ...


2

VoltDB has very limited support for triggers in DDL, the way some traditional SQL databases do. You can tell it to run a SQL statement when a table reaches a certain row count, which is often used to truncate older data. Within stored procedures, there is tremendous support for very flexible logic. You can perform arbitrary database operations when certain ...


0

INSERT INTO table_2 will always insert and never update. If you need different operation depends on which operation(insert or update) actually fired the trigger, you need to check it with IF UPDATING THEN ... END IF; or (IF INSERTING THEN ... END IF; )


1

ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user. If you want to select constraints of a table which belongs to another schema then you should have select privilege(even if you have dba role assigned to the user) on that table. ...When I copy the whole insert query and run it (with the same user) from sql ...


0

Finding all nodes in a subtree to update them requires a recursive common table expression: -- update the root of all nodes in 'a's subtree to 'x' WITH RECURSIVE subtree(id) AS ( VALUES('a') UNION ALL SELECT Node.id FROM Node JOIN subtree ON Node.parent = subtree.id ) UPDATE Node SET root = 'x' WHERE id IN subtree; In SQLite, CTEs are ...


0

This will update Error to NULL when names are not the same in your update. It will not handle NULLs. If name is nullable, then you will need to handle in the trigger. ALTER TRIGGER [dbo].[ERROR_CLEAR] ON [dbo].[contact] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF UPDATE (NAME) BEGIN UPDATE c2 SET ERROR = Null FROM contact c2 INNER JOIN ...


0

A sliding time scale is very efficient by using PARTITIONs. Implement 14 hourly partitions using PARTITION BY RANGE(..). Use REORGANIZE PARTITION to create a new partition every hour. Use "transportable tablespaces" (assuming you are using 5.6, or preferably 5.7, and InnoDB) to move the 'old' partition away from the main table to the archive table. More ...



Top 50 recent answers are included