Tag Info

Hot answers tagged

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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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



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