New answers tagged

0

All the DBMS vendors suggest to create all the bussines logic in the database because that will asures the dependency to specific DBMS. To my students I recommend to analyze each situation, new capabilites like column store or in memory structures help you to improve your app performance moving some logic to the database and moving less information between ...


2

The long and the short answer is no! This is yet another "non-feature" of MySQL. This construct is known more commonly as CTAS (CREATE TABLE AS.... SELECT) - the CREATE TABLE LIKE syntax is non-standard - like so much of MySQL! MySQL's documentation argues here - that this lacuna is to ensure "flexibility" - you'd laugh if it wasn't so sad! CREATE TABLE ...


4

A computed column would solve the issue and probably be better than having triggers: CREATE TABLE #Moofasa ( gotitdate DATE, dunitdate DATE, delayed AS COALESCE( DATEDIFF(day, gotitdate, dunitdate), 0) ) ; Tested at: rextester.com: INSERT INTO #Moofasa (gotitdate, dunitdate) VALUES ('2016-07-01', '2016-07-05'), ('2016-07-01', '2016-07-04'), ...


1

Best practice... A database is a place to store data. It is the 'source of truth'. The application is the place for * interpreting the data * "Business logic" * Formatting and pretty-printing of output * Cleansing the data as it is stored into the database. It is often wise (especially in more complex systems) to have a "database layer" in which the "...


0

Got the solution: CREATE TRIGGER test_utrig ON test FOR UPDATE AS if UPDATE(id) and exists(select id from inserted where id not in (select id from inserted)) begin insert into test_a select 'update', UPDATE_BY(), GETDATE(), inserted.* FROM inserted


0

There is no SPLIT_STR(). But a couple uses of SUBSTRING_INDEX() will do the trick.


0

You cannot get it automated via FOREIGN KEY. Plan A: Use a TRIGGER. Plan B: Use INSERT .. ON DUPLICATE KEY UPDATE ...


3

The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE. And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there are ways around this limitation: Proof of concept Assign to NEW by key in a Postgres trigger How to set value of composite variable field using dynamic SQL ...


0

As suggested in the comments, it looks like the closest I can do to what I want is (fixing up my original approach in the question): CREATE OR REPLACE FUNCTION insert_purchaseview_func() RETURNS trigger AS $BODY$ DECLARE tmp RECORD; BEGIN WITH input (product_name, when_bought) as ( values (NEW.product_name, NEW.when_bought) ) INSERT ...


1

You can use IFNULL(see here) for NULL only or IF(see here) when you're going to include empty strings when setting the value for NEW.NAME. BEFORE INSERT when you have to check the record first before inserting the record. You can try the below query: DELIMITER $$ DROP TRIGGER IF EXISTS `checkIfNull`$$ CREATE TRIGGER `checkIfNull` BEFORE INSERT ON `CHILD`...


1

Tables: create table PARENT ( ID BIGINT PRIMARY KEY, NAME VARCHAR(255) NOT NULL ); create table CHILD ( ID BIGINT PRIMARY KEY, NAME VARCHAR(255) NOT NULL, PARENT_ID BIGINT NOT NULL ); Trigger: DELIMITER $$ CREATE TRIGGER namecheck BEFORE INSERT ON CHILD FOR EACH ROW BEGIN DECLARE parentname VARCHAR(255); IF NEW.NAME IS NULL OR NEW.NAME='' ...


2

Man, I'm terribly sorry. I'm 'answering' stating that there's no simple solution as the one you devised. You presented us a very interesting challenge. I've been doing research and exercises in the past four hours to assure there's no way one can collect the aftermath of a committed transaction relying upon SQL Server engine by itself. If you, please, ...


0

Thanks to @ Justin Cave I got the answer. The problem was that another table (Department) was referencing the Store table as well. so I added in the trigger body: DELETE FROM Department WHERE Department.storeCode = :oldRow.stCode;


5

Very easy to test. CREATE TABLE T(X INT); GO INSERT INTO T SELECT TOP 10 1 FROM sys.all_objects; GO CREATE TRIGGER TR1 ON T INSTEAD OF DELETE AS DELETE TOP (8) FROM T; GO CREATE TRIGGER TR2 ON T AFTER DELETE AS SELECT COUNT(*) FROM DELETED; GO DELETE FROM T; DROP TABLE T; Answer 8 This is the correct behaviour. The after trigger should ...


1

I cross-posted this to Stackoverflow and found a working answer, even if it meant having to type out the column names (which explains why my WHERE clause didn't work) SELECT ins.[name], ins.[created_at] FROM INSERTED ins JOIN DELETED del ON ins.id = del.id WHERE del.[name] <> ins.[name] OR del.[age] <> ins.[age]; ...


2

ALTER TRIGGER [dbo].[t_upd_insert] ON [dbo].[Player] FOR INSERT, UPDATE AS SET XACT_ABORT, NOCOUNT ON; BEGIN IF (update(name)) BEGIN INSERT INTO dbo.Changes ( [name] [created_at] ) SELECT ins.[name], ins.[created_at] FROM INSERTED ins END



Top 50 recent answers are included