Tag Info

New answers tagged

0

It depends on when the trigger fires during the transaction. The trigger itself is part of the transactional context you are currently in. This means that any changes you have already made in the transaction so far will be visible to the trigger. To take a simple example, consider this data model: CREATE TABLE Foo (F INT) CREATE TABLE Bar (B INT) INSERT ...


1

The error you're getting indicates that the User1 user does not have access to the dbms_job package. You'd need a DBA to GRANT EXECUTE ON dbms_job TO user1; If you create the trigger and procedure to be owned by a user other than the one that owns the table, you'd need to include the schema name in your GRANT statement inside the procedure. EXECUTE ...


0

For one thing DBMS_JOB.submit requires COMMIT and you lack that. Another thing is that DBMS_JOB is ugly and long deprecated. Use another guide/howto, one written in this millennium.x And you don't need WITH GRANT. Retaining this answer only to keep informative comments.


1

It is actually possible to do this neatly without using triggers if you are using InnoDB. Create another table with just one column. That column should have a foreign key (hence the innodb requirement in this solution) that points to the immutable column of the original table in question. Put a restriction like "ON UPDATE RESTRICT". In summary: CREATE ...


0

From here and here I wrote the code below which should work in 5.1 - it inserts an error in a different table - but it could be changed - at least the syntax works. Here is the trigger DELIMITER $$ CREATE TRIGGER mytabletriggerexample BEFORE UPDATE ON billy FOR EACH ROW BEGIN IF(NEW.fred = OLD.fred) THEN BEGIN DECLARE dummy INT; // if you want ...


0

You have two options OPTION #1 : Make the Column Immutable DELIMITER // CREATE TRIGGER update_table BEFORE UPDATE ON table FOR EACH ROW BEGIN IF (NEW.updated_replication < OLD.updated_replication) THEN NEW.updated_replication = OLD.updated_replication; ELSE SET NEW.updated_replication = NOW(); END IF; END // DELIMITER ; ...


0

I indented everything and quickly noticed that the last END must have $$ after it, not the ; USE `gknet`; DELIMITER $$ CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` FOR EACH ROW BEGIN IF NEW.loggedin != OLD.loggedin OR NEW.sessionkey != OLD.sessionkey THEN IF NEW.loggedin = '1' THEN INSERT INTO user_records (userid, ...


0

In MySQL you can insert directly into AUTO_INCREMENT Column. So, take this example from MySQL manual: CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM; INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich'); SELECT * FROM ...


0

Try this (killed me for 1/2 an hour... Doh...). Note the use of ELSEIF (and NOT ELSE IF... compiler should have picked that up! :-) ) - only did 1 - should work for the others. DELIMITER $$ CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` FOR EACH ROW BEGIN IF NEW.loggedin != OLD.loggedin OR NEW.sessionkey != OLD.sessionkey THEN IF ...


-2

the code erro ORA-04091 is relative the transaction. In a trigger you dont can execute select, unless you declare a new transacation with clause PRAGMA AUTONOMOUS_TRANSACTION. remember to run a commit or rollback to finalize transaction on the trigger.


1

For DML changes you can utilize any of the followinh native SQL Server auditing features: SQL Server Change Tracking SQL Server Change Data Capture SQL Server Auditing Each has its advantages and disadvantages, but the Auditing is the latest introduced by Microsoft, so it would be a good idea to build your current and future solutions wrapped with it. ...


0

Thanks for answering..I have implemented it in a way as below: I had used Symmetric DS, I found it useless and very cumbersome to configure and use. And then, I used the mysql UDF for http requests Published a service using Oracle Embedded pl/sql gateway, esp. a procedure that accepts parameters, that lets Oracle db know the number of rows it has to pull ...



Top 50 recent answers are included