In relation to my other question "What’s the best way to audit log DELETEs?". What's the PostgreSQL equivalent of CONTEXT_INFO? [EDIT] I want to log deletes using trigger, but since i'm not using the database user as my app's logical user, I cannot log the CURRENT_USER from the trigger code as the user who deleted the record. But for...
I'm looking for a good reference card / cheat sheet that compares T-SQL and PL/SQL data manipulation language commands side-by-side. I've previously searched SO but there isn't any older thread covering this particular subject so I thought it'd be a good idea to start one, given that such reference is almost mandatory if you happen to w...
Hi I have three tables, t1, t2 and t3. 80% of the fields/columns are similar in these tables. Around 75,000 records are posted in table t1 and t2 on daily basis, totaling to 150,000. A process, say P123, is executed which moves 40-60 % records from t1 and t2 to t3 in a pair. The user searches frequently for records in all three tables wi...
I am working on an application that uses Oracle's built in authentication mechanisms to manage user accounts and passwords. The application also uses row level security. Basically every user that registers through the application gets an Oracle username and password instead of the typical entry in a "USERS" table. The users also receive ...
I have a table with a column that I want to extract out and put into a separate table. For example, lets say I have a table named Contacts. Contacts has a column named Name which stores a string. Now I want to pull out the names into another table named Name and link the Contact.Name column to the Id of the Name table. I can only use ...
I'm developing an application which requires multiple replicas and uses syncrhonization to keep replicas up to date with each other. To do this, I'm taking advantage of SQL Server 2008 change tracking feature. The one hole in this strategy appears when tracking who made a particular change in a replica. SQL Sever 2008 provides for thi...
I understand that DML technically encompasses SQL verbs which merely query but do not modify persistent data. (See, e.g., wikipedia or oracle or orafaq) However, I often wish to refer to "all and only those SQL verbs which modify stored/persistent data" -- basically INSERT, UPDATE, DELETE but not a plain SELECT. Is there an official/st...
The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements. Now, technically, it is possible to use SELECTs withou...
Hello is possible to switch between DML commands/operations (Insert,Delete,Update) on Trigger Body?, I try to snippet some T-SQL for understand me better : CREATE TRIGGER DML_ON_TABLEA ON TABLEA AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; CASE WHEN (INSERT) THEN -- INSERT ON AUX TABLEB WHEN (DELE...
I am trying to find impact of doing DDL statement against deleted and inserted logical tables inside table trigger. I have: CREATE TRIGGER [Trigger52] ON [dbo].[Table1] FOR DELETE, INSERT, UPDATE AS BEGIN create table inserted (c1 int) select * from inserted END When it is triggered, I expected to g...
I am catching errors from a bulk insert operation like this: begin --bulk insert forall i in v_data.first .. v_data.last save exceptions insert into my_filter_table values v_data (i); commit; exception -- catch and print the saved-up DML errors. when X_DML_ERRORS then declare v_iteration...
Hi all, I'm importing 4.1mil records into an offline system to do some analysis on a subset of our database. As I'm running the import, I'm trying to check its progress by using: SHOW TABLE STATUS LIKE 'MailIssueElement' What's odd...at different times, I'm seeing higher AND lower values for Rows. I would expect it to only go up. ...
Am I correct in understanding that mysql's LAST_INSERT_ID() function doesn't reset between insert attempts (as @@identity does in sql-server)... that if the immediately preceding insert operation fails, LAST_INSERT_ID() will return the pk of whatever the pk of that connection's last insert to any table with an auto-incrementing primary k...
Is Commit a DML instruction in PL\SQL? ...
What ways are there to combine executing of a stored procedure and using it's result or parameters in a regular SQL query? Or not supported yet but planned in future versions of SQL Server. I'm afraid that I use variables when it's possible do not. I mean next: -- passing result of SELECT to SP SELECT a, b FROM t EXEC my_sp a, b -- pa...
I'm trying to write an SQL statement that will generate an SQL script that will update a BLOB field with an IMAGE being selected from the database. This is what I have: select concat( 'UPDATE `IMAGE` SET THUMBNAIL = ', QUOTE( THUMBNAIL ), ' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT from IMAGE; In t...
Can I use DML in from clause in DB2? thank you ...
UPDATE CustomerPhone SET PhoneTypeID = 7, PhoneNumber = 999-444 WHERE CustomerID = 500 AND PhoneNumber = 9-1-1; PhoneNumber is of type varchar(20) whereas PhoneTypeID and CustomerID are of type int. I'm running the above statement in SQL Server, it works fine. I wonder how come it works? I thought any string value has to be put betwe...
I have two identical tables in two different databases with the same data but they have different primary keys, I need to update these so they have the same key, so what I did was making sure that none of the tables had any key in common and that there were no duplicates UPDATE db1.dbo.Table SET db1.dbo.Table.pcol = rightPcol.pcol FROM ...
Is it possible to see the DML (SQL Statement) that is being run that caused a trigger to be executed? For example, inside an INSERT trigger I would like to get this: "insert into myTable (name) values ('Fred')" I read about ora_sql_txt(sql_text) in articles such as this but couldn't get it working - not sure if that is even leading me...