Way to reference a column dynamically within a trigger

oraclestored-procedurestrigger

I asked a similar question a few months back and I'm improving upon my solution!

I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of another.

For example, say that if I mark an item on sale, I would like the database to automatically take 20% off the price. So:

| ID | ITEM                     | PRICE | ONSALE | DISCOUNT1 | DISCOUNT2 |
|----|--------------------------|-------|--------|-----------|-----------|
| 1  | T-Shirt                  | $20   | F      | .15       | .3        |
| 2  | Sweatshirt               | $30   | F      | .25       | .5        |
| 3  | Jeans                    | $40   | F      | .1        | .4        |
| 4  | Fashionably Ripped Jeans | $120  | F      | .05       | .1        |

What if I wanted the trigger to, say, programmatically select DISCOUNT1 or DISCOUNT2, how would I do this?

:NEW.PRICE := :NEW.PRICE - 
    (:NEW.PRICE * :NEW.DISCOUNT(do tricky math to get a random number));

Best Answer

I was not able to solve this issue in the method I had wanted. I used an if statement as suggested by Serg.

if ((:NEW.ONSALE = 'T') AND (:OLD.ONSALE = 'F')) then
    :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT1;
elsif ((:NEW.ONSALE = 'T') and (:OLD.ONSALE = 'T')) then
    :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT2;
end if;

This was a very simplified example but I couldn't figure out a better way to do this.

Related Solutions

Mysql – way to tell your hostname within a stored procedure

I can see my hostname on my PC

mysql> show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)

mysql>

This works in MySQL 5.x

SELECT variable_value INTO @local_hostname
FROM information_schema.global_variables
WHERE variable_name = 'hostname';

The query works directly in the mysql client:

mysql> SELECT variable_value INTO @local_hostname
    -> FROM information_schema.global_variables
    -> WHERE variable_name = 'hostname';
Query OK, 1 row affected (0.00 sec)

mysql> select @local_hostname;
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

mysql>

Here is quick SP I wrote in MySQL 5.5.12 on my PC

DELIMITER $$

DROP PROCEDURE IF EXISTS `junk`.`ShowHostName` $$
CREATE PROCEDURE `junk`.`ShowHostName` ()
BEGIN

    SELECT variable_value INTO @local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';
    SELECT @local_hostname;

END $$

DELIMITER ;

Then run CALL junk.ShowHostName();

mysql> CALL junk.ShowHostName();
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Here it is as a function

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetHostName` $$
CREATE FUNCTION `junk`.`GetHostName` () RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN

    DECLARE local_hostname VARCHAR(64);

    SELECT variable_value INTO local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';

    RETURN local_hostname;

END $$

DELIMITER ;

Now, just SELECT it:

mysql> SELECT junk.GetHostName();
+--------------------+
| junk.GetHostName() |
+--------------------+
| LW-REDWARDS2       |
+--------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

Postgresql – Passing argument in trigger dynamically

If you want to pass data to a trigger that is not provided by one of the predefined variables you could put that data in a temp table before you perform the INSERT/UPDATE/DELETE that will fire the trigger. Then you can retrieve the data from the table inside the trigger function. Temp tables are local to sessions so this is safe to do when multiple clients connect to the same database.

Best Answer

Related Solutions

Mysql – way to tell your hostname within a stored procedure

Postgresql – Passing argument in trigger dynamically

Related Question