Tag Info

New answers tagged

0

Don't use a trigger - they extend transaction processing time, are difficult to debug if you have issues, cause deadlocks, etc. Update the emailupdate in the same statement as you are updating the email. UPDATE myTable SET email = 'testemail@gmail.com', emailupdate = curdate() WHERE email = 'PreviousEmail@aol.com';


1

As @dezso says, you can't compare NULL with arithmetic comparison operators as described in the MySQL's documentation. You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. To demonstrate this for yourself, try the following query: mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; ...


3

Just keep one table. Then replace the other table with a view on the table. Now you should be able to insert/update/delete a row in either the view or the table. Normally this should work. If the insert/update/delete in the view cause a problem then you can always create triggers on the view that replace insert, delete, and update (create trigger t_x instead ...


1

Yes, TIMESTAMPDIFF is the best approach: select TIMESTAMPDIFF(year,'2011-01-01', now() ) ; --> 4 select TIMESTAMPDIFF(year,'2011-08-28', now() ) ; --> 4 select TIMESTAMPDIFF(year,'2011-09-01', now() ) ; --> 3


0

I can't comment on these boards, but really credit should go to @Vérace. Look at the TimeStampDiff, it looks like it should subtract/add your dates and can output the result in months. Note that without the timepart, it will use 00:00:00 as the timepart. EDIT: SET NEW.montant_annuel = NEW.montant * (TIMESTAMPDIFF(MONTH, NEW.début, ...


2

The list of events that can be used for DDL Triggers can be found on the following MSDN page: DDL Event Groups. If you look through that list, you will notice that they do not offer a level of granularity below the base CREATE / ALTER / DROP {ObjectType} ... So trapping ALTER_TABLE will get all ALTER TABLE... statements. Once a DDL Trigger is fired, you ...


0

Yes, this should work (untested): CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan() -- generic name RETURNS trigger AS $func$ DECLARE _timetype varchar; _timetypespan_resume interval; _ct int; BEGIN CASE NEW.timetype WHEN 'lap' THEN EXECUTE format($$ SELECT timetype, timetypespan, age($1, timestmp) FROM %s WHERE ...


1

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this: CREATE OR REPLACE FUNCTION trfn_tbl_log_any() RETURNS trigger AS $func$ DECLARE _ct int; BEGIN IF NEW.timetype = 'start' THEN EXECUTE format($$ SELECT floor(t.timeidx) + 1 ...


1

Or you can use TG_RELID, but since its data type is plain oid, not regclass, one must cast it to regclass explicitly to get the auto-conversion to a schema-qualified (only if the current search_path requires it), cleanly escaped table name. The documentation: TG_RELID Data type oid; the object ID of the table that caused the trigger invocation. ...


2

The actual syntax corresponding to the imaginary SELECT columnname FROM %currenttable% would be, in plpgsql: execute format('SELECT columnname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME); The TG_* built-in variables are documented in Trigger Procedures and the execute and format plpgsql constructs in Basic Statements. The query above is ...


1

Every trigger have some internal tables , INSERTED and DELETED. In this case, we use only the INSERTED table, which hold the records inserted. ALTER TRIGGER [xxx] ON [dbo].[Main] AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO dtsx.dbo.main(refno,subject,recdate,dateind,officefrom,author,status) SELECT ...


2

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want: BEGIN EXECUTE format('SELECT colname FROM %I', TG_RELID) END; or BEGIN EXECUTE format('SELECT colname FROM %I.%I', ...


0

There are several options, but here are two based on two options for how your end-users connect. First, if your users connect as themselves. For example, if you have a user "annamaria" then in your trigger you can identify the user with the CURRENT_USER() function. Record CURRENT_USER() into your auditing table with each modification and you can track ...


1

Depends on what version you are. You can install the audit_plugin: INSTALL PLUGIN server_audit SONAME 'server_audit'; You can choose two outputs, file or syslog. The output format will be: 20140901 15:19:44,localhost.localdomain,root,localhost,4,133,WRITE,employees,salaries, 20140901 ...


0

There is no CREATE TRIGGER IF NOT EXISTS There is DROP TRIGGER IF EXISTS You must execute the trigger's creation as follows: USE xxx_admin DELIMITER $$ DROP TRIGGER IF EXISTS test_AFTER_UPDATE $$ CREATE DEFINER=`root`@`localhost` TRIGGER `xxx_admin`.`test_AFTER_UPDATE` AFTER UPDATE ON `test` FOR EACH ROW BEGIN INSERT INTO auditTest select *, ...


3

That will work fine - however you need to add a delimiter to your trigger: DELIMITER $$ CREATE DEFINER=`root`@`localhost` TRIGGER `admin`.`company_AFTER_UPDATE` AFTER UPDATE ON `company` FOR EACH ROW BEGIN INSERT INTO AuditCompany select *, now() from Company where id = NEW.id; END $$ DELIMITER ; You might gain some performance benefit if you ...


1

Get rid of UNIX_TIMESTAMP() everywhere. DATETIME fields can be compared directly. Once you have done that, you will probably notice the missing @ that is causing the error. I think it can be simplified down to only: CREATE TRIGGER ... IF COALESCE(OLD.updateDate, OLD.insertDate) < CURRENT_DATE() - interval 3 month INSERT INTO ...


4

An INSTEAD OF INSERT trigger performs actions instead of what the original INSERT would have done. In your code, if either @appr_username or @appr_id is NULL, some sort of change is made to the base table (an insert or an update). Otherwise, nothing is done by the trigger (no rows affected), so the AFTER triggers are skipped. After all, SQL Server thinks, ...


1

I worked with a very similar situation like this in the past in a 24x7 manufacturing company and finally decided to use transactional replication. It is possible to configure DDL to be replicated such that you can push out whatever the patches change to the subscriber. Obviously there are pros and cons to everything and you need to weigh them to determine ...


0

In one situation, I found it possible to ingest data faster into InnoDB than into MEMORY. Perhaps you should simply get rid of the MEMORY table and the TRIGGER. I discuss that an many other tidbits in my high-speed ingestion blog. Do you have multiple inserters? Do you insert multiple rows in a single INSERT? Are you pulling the data out of the MEMORY ...


3

By all means put it into a standardised set of tables so that you can tweak the import stage rather than having to change complex report(s) and queries. But the data should still be normalised which will require multiples tables (but with good indexes). As others have mentioned, don't use triggers, sync in batches. Don't worry about lots of joins, when ...


2

My plan was to write all of these records to one "catch-all" table, and write triggers on the original tables to maintain the records in this aggregate table. Triggers have so many problems you should avoid them: An error in a trigger can cause the original transaction to abort Triggers that correctly handle multi-row operations are hard to write ...


7

If I understood you correctly, you have a large third-party system, you don't have much control over it, you make complex reports that read data directly from this third-party database, your queries depend on the internal structure of the third-party database. I would approach it like this: Set up my own separate database, which I have full control ...


1

Not a complete answer, but it would not fit into a comment. lastval() & currval() What makes you think lastval() is discouraged? Seems like a misunderstanding. In the referenced answer, Craig strongly recommends to use a trigger instead of the rule in a comment. And I agree - except for your special case, obviously. The answer strongly discourages ...


4

So, is there a way to preserve current_user, without giving the dbuser group role direct access to the relations in schema private? You may be able to use a rule, rather than an INSTEAD OF trigger, to provide write access through the view. Views always act with the security rights of the view creator rather than the querying user, but I don't think ...


1

It sounds like you lack the SUPER privilege (global permission). When you connect to MySQL, run SHOW GRANTS; If you see more than one line, you do not have SUPER. What does having SUPER give you ? The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill ...



Top 50 recent answers are included