Tag Info

New answers tagged

0

you are almost on the right track with a slight misunderstanding. But I have a lot of tables and I don't one to create a trigger on them one by one. This is not correct. You just have to create SERVER LEVEL / DATABASE LEVEL TRIGGER that will take care of the database events that occur on the server instance or database. You can even filter out the ...


1

The actions you're performing on the data is technically different between the insert/update portions, but what about the business logic difference? IMO, your best bet is to contain a functional unit of work inside of a trigger, so that there is no discrepancy in what that trigger does. In my experience, Developers/Business Analysts often tend to think in ...


0

You need to use the Delimiter /// first if you are creating a trigger. Delimiter /// CREATE TRIGGER Section_Insert AFTER INSERT ON Section FOR EACH ROW BEGIN INSERT INTO Audit(changeTime, tableName, Action) VALUES (NOW(), 'Section', 'INSERT'); End; ///


1

I was going to write a long post telling you that there are basically 3 ways to implement polymorphic associations, but someone (@Bill Karwin) has already done it better in a more compact way: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32 Your original solution is the first one presented on the slides, the one you propose is the 3rd ...


1

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



Top 50 recent answers are included