Hot answers tagged

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


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


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


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


2

This is not valid JSON; strings require double quotes. Anyway, the table-valued function json_each() allows to enumerate a dynamically-sized array: SELECT * FROM json_each('[["c2","c1","clone"],["c1","n0","clone"],["n0","p0","offspring"],["p0","a2","offspring"],["a2","a1","offspring"],["a1",null,null]]'); key value type atom ...


2

Don't assume that the trigger will always handle a single row inserted in the table. If you're inserting more than one row in Rental, the code you attempted to write will fail. The logical tables inserted and deleted contain all the rows modified by the DML operation and they must be joined or correlated to the base table (or other tables) in order to take ...


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


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


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

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


1

BEFORE inserting, use OLD. Is this building something like a "Summary table" for a Data Warehousing application? If so, consider doing: INSERT INTO tmp VALUES ...; INSERT INTO summary_table SELECT SUM(...),... FROM tmp GROUP BY ...; INSERT INTO real SELECT * FROM tmp; There are many variants on that flow. But note that you get a chance to transform ...



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