Tag Info

New answers tagged

0

Got the answer. To remove "FROM" away from INNER JOIN thx


2

Even with SQL 2000 Service broker is still your answer. I'm not going to go into how to set up a service broker solution here because to be honest it's complicated and I've only done it once. That being said, design your service broker system in 2008 and 2008 R2. For your 2000 system design a second SB system on one of your 2008 R2 systems (let's use ...


5

I think I would build a table with a queue of some sorts, and have a job poll that table and pick up any work it has to perform. So for example your trigger writes a record id in a table called processqueue, then you have a job that picks up all the records from the processqueue table and do it's heavy lifting for each and every record, removing the record ...


0

I believe I have found the solution, and that is to convert the Procedure to a Function. DELIMITER $$ CREATE FUNCTION fn_get_item_type_id( item_name varchar(45) ) RETURNS INT DETERMINISTIC RETURN (SELECT CASE WHEN LEFT(`item_name`,6) = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = ...


0

You can detect any ddl changes by using trace file. below is script to get changes. SELECT te.name AS eventtype ,t.loginname ,t.spid ,t.starttime ,t.objectname ,t.databasename ,t.hostname ,t.ntusername ,t.ntdomainname ,t.clientprocessid ,t.applicationname FROM sys.fn_trace_gettable ( CONVERT ...


0

mysql> describe persons; +------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+-----------------------------+ | id | int(11) | NO | PRI | NULL ...


0

You need to change the delimter to $$ and then back to ;: DELIMITER $$ CREATE TRIGGER `before_delete` BEFORE DELETE ON `persons` FOR EACH ROW BEGIN DELETE FROM ldap_entries WHERE ldap_entries.keyval = OLD.id END $$ DELIMITER ;


1

For 5.5 and later it is possible to use signals: delimiter @ create trigger checkcollision after update on players for each row begin declare dummy int default 0; select 1 into dummy from walls where x=NEW.x and y=NEW.y; if (dummy = 1) then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message'; end if; end @ delimiter ; ...


1

Use record type OLD to access the old values as you do NEW to access the new values when updating a record in a table. Refer: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html


4

I have another variation of the formula guaranteed to work (unless there is a power failure) SET @now = NOW(); SET @nextsat = DATE(DATE(@now) + INTERVAL (5-WEEKDAY(@now)) DAY + INTERVAL 570 MINUTE + INTERVAL IF((HOUR(@now)*3600+MINUTE(@now)*60+SECOND(@now))>34200, IF(WEEKDAY(@now)=5,1,0),0) WEEK); In case you are wondering, 09:30:00 AM is ...


0

its okay, and sorry for the bad explanation. i got it to work as shown below: CREATE OR REPLACE FUNCTION tf_xy_point() RETURNS trigger AS $BODY$BEGIN IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN new.easting := ST_X(new.wkb_geometry); new.northing := ST_Y(new.wkb_geometry); RETURN NEW; END IF; END;$BODY$ LANGUAGE plpgsql VOLATILE COST ...


6

You can use WEEKDAY and DATE_ADD function to calculate the next weekday incoming. Here what you have to do: SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY); The query meaning: With DATE_ADD you will add an interval between the parameter NOW() and one condition assuming my weekday for Saturday is 5, you ...


0

The problem was an error in my trigger insert code. It was using INNER JOIN's and one was not correct so was not returning any data.


1

I've made it in a TRIGGER calling a Stored Procedure inside resultsTBL table. Table lotteryTBL and resultsTBL: mysql> SELECT * FROM test.lotteryTBL; +----+------+------+------+------+------+------+-------------+ | ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew | +----+------+------+------+------+------+------+-------------+ | 1 | 1 | ...


1

Your syntax is wrong. I don't think putting a loop inside a TRIGGER is a good idea, but I made you a Stored Procedure as example: Procedure: USE `test`; DROP procedure IF EXISTS `new_procedure`; DELIMITER $$ USE `test`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`() BEGIN SET @counter=0; SET @column=''; WHILE (@counter < 6) DO SET ...


0

Has to be a PROXY situation. As MySQL's documentation says: When authentication to the MySQL server occurs by means of an authentication plugin, the plugin may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to ...


1

SET GLOBAL event_scheduler = ON; -- enable event scheduler. SELECT @@event_scheduler; -- check whether event scheduler is ON/OFF CREATE EVENT e_store_ts -- create your event ON SCHEDULE EVERY 24 HOURS -- run every 24 hours DO UPDATE myschema.youtable set mycolumn='1'


2

You could use log_output and general_log (USE CAREFUL), for those blessed with MySQL >= 5.1.12: Steps: I've created a new table called new_table: CREATE TABLE new_table ( id int(11) NOT NULL, text varchar(45) DEFAULT NULL, text2 varchar(45) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I've created a new log table called ...


2

The measured difference is almost certainly noise. Run some more iterations, you won't get consistent result. The difference in performance (if any exists) won't be measurable. You can use either method here. Both are equally good for the purpose. There are often multiple ways in SQL. And sometimes there is no clear winner. The more important issue ...


0

You can't run a command inside a Stored Procedure but you can get a outfile with SELECT ... INTO OUTFILE FILE: SELECT field_1 INTO OUTFILE '/tmp/abc.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';


1

You just need to use a AFTER DELETE TRIGGER: USE `test`; DELIMITER $$ DROP TRIGGER IF EXISTS test.for_user_user_AD$$ USE `test`$$ CREATE DEFINER = CURRENT_USER TRIGGER `test`.`for_user_user_AD` AFTER DELETE ON `for_user_user` FOR EACH ROW BEGIN INSERT INTO `fos_user_user` (`id`, `username`, `username_canonical`, `email`, `email_canonical`, `enabled`, ...


0

Remove the Exception block so you no longer catch the error. Then the error will propagate out and prevent and prevent the insertion from occurring.


2

You have an unresolved naming conflict. You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting. Here you declare a variable named measurement_id: DECLARE measurement_id INTEGER; It's a folly to use ambiguous variable names to begin with. If you do it anyway, you ...


3

Your UPDATE statement is wrong. I've edited your TRIGGER and the modifications I did and errors I saw: Errors: You tried to drop a different TRIGGER that the one you're going to create. DROP TRIGGER IF EXISTS veevan.account_ai$$. Why do you need the variable @TargetLastMod?. You didn't use it in the whole trigger. Modifications: I edited the DROP ...


1

I've modified your TRIGGER and I used ON DUPLICATE KEY UPDATE to UPDATE all the fields when your NEW.lastmodifieddate be greater than targets.lastSyncAt. Some errors: You've used a function on a field label in pdone.LCAPITAL(first), that can't be possible. You've used the same function now trying to CONCAT 2 field labels, instead table fields name ...


1

You need to replace BEGIN ATOMIC with just BEGIN in the trigger body. BEGIN ATOMIC indicates an inlined compound SQL statement, BEGIN -- a compiled compound statement. Documentation states: If multiple events are specified, the triggered action must be a compound SQL (compiled) statement.


1

I edited your TRIGGER to do this: I added the vTerritoryCount variable. If you have more than 1 rows with the same userid it wont update the reps.territories_id. Here's the TRIGGER: USE `veeva_new`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW BEGIN # [ VARIABLES ...


0

Your trigger should look like this to handle UPDATE with multiple affected rows: CREATE TRIGGER AccountChange ON dbo.Customer AFTER UPDATE AS BEGIN UPDATE dbo.Customer SET AccountTypeID = 8 FROM Inserted i INNER JOIN Deleted d ON i.AccountId = d.AccountId -- use the primary key here WHERE AccountNumber = i.AccountNumber AND ...


4

since INSERTED can contain more than one row the update could be performed using a join and not fillin single valued variables: UPDATE C SET AccountTypeID = 8 FROM Customer as C on INSERTED as I on I.AccountNumber = C.AccountNumber WHERE I.TotalSales between 0 and 5000


0

Instead of creating a trigger I would suggest you to create a new column in the purchase table which will be a computed column. Before creating it, create a scalar function which will accept productname or productid and returns int. Inside it, write a query to find the total from sales for that particular row (by passing productname or id) and subtract it ...


0

I don't think that a trigger is the right place for your application business logic. A stored procedure to update the corresponding rows in the stock table would be much more sensible in my opinion. If you want to prevent users from modifying the sales and purchase tables directly without updating the stock table, don't grant permissions on those objects ...


1

Actually, this is all you need: NEW := jsonb_populate_record(NEW, NEW.json); Per documentation: jsonb_populate_record(base anyelement, from_json jsonb) Expands the object in from_json to a row whose columns match the record type defined by base (see note below). What's not documented: The row provided as first argument retains all values that ...


1

You already found that you have to use dynamic SQL with EXECUTE. What you are missing: If child tables are not guaranteed to share the same row type , you must add a target column list to your INSERT statement or you are bound to run into errors or worse: it might work in surprising ways. You need to defend against SQL injection. Table names have to be ...


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 later, things will get confusing fast. A nice set of instructions in a stored procedure or client side procedure can get across the business logic more clearly than a bunch of hidden logic in the database. There are also limitations ...


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



Top 50 recent answers are included