Is it true that all data modifications are undone when the transaction they are a part of gets rolled back? For example if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back? Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW? What about DROP DATABASE? I know certain statements still execute though like PRINT "MESSAGE". I'm just trying to get an idea of what kinds of events still occur.

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider backup).

I have a sql table with two fields TermID and IP address. I want to insert(10.100.08.01-10.100.08.254) and each IP will have an assigned TermID for example (TERM1 - TERM254). I will greatly appreciate any guidance as this is not my expertise.

I'm administering an Oracle 12c database and need to understand how the database handles the execution order of DML (Data Manipulation Language) statements when executed across different transactions initiated by distinct connections. Here’s the specific scenario I'm dealing with: Transaction 1: Connection A initiates a transaction and executes a series of identical UPDATE statements:

update my_table set col1 = val2 where primary_key = 1;
update my_table set col1 = val2 where primary_key = 2;
...
update my_table set col1 = val2 where primary_key = 50;

Transaction 2: Connection B starts a transaction almost simultaneously, executing the same series of UPDATE statements in the same order. Questions: 1. Execution Order: Will Oracle 12c ensure that these UPDATE statements are executed in the same order across both transactions? 2. Influencing Factors: What factors could influence the execution order of these DML statements across different transactions and connections? I've reviewed the Oracle 12c documentation, particularly on transaction management and DML execution, but couldn’t find clear guidance on whether Oracle guarantees consistent execution order in such scenarios. I would appreciate any insights, particularly on how Oracle handles transaction scheduling or any internal optimizations that might affect the execution order

I have a PostgreSQL trigger that is not firing sometimes, even though the status is always shown as "enabled". My trigger code is as follows:

CREATE OR REPLACE FUNCTION audit_src_exhibit() RETURNS trigger AS $BODY$
BEGIN
  IF TG_OP = 'INSERT' then
		if new.audit_created_date is null  THEN
		new.audit_created_date := current_timestamp;
		new.audit_created_by := session_user::text;
	end if;
	else
		if new.audit_modified_date is null  THEN
		new.audit_modified_date := current_timestamp;
		new.audit_modified_by := session_user::text;
	end if;
END IF;
  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER audit_src_exhibit_tr
BEFORE INSERT OR UPDATE ON 
FOR EACH ROW EXECUTE PROCEDURE audit_src_exhibit();

Is there any specific reason for this behaviour? Does my code show any signs of known issues which would result in triggers not firing? I am getting the audit columns as empty even though some insert and update happened today

I started writing this post as a question but, while reproducing the error, I found the solution in the PostgreSQL documentation. So I'll share the solution here for the community. A PostgreSQL 14 table had some data deleted accidentally.

sql
    -- Creating table
    CREATE TABLE my_table (
        id            integer      not null  primary key  generated always as identity,
        description   varchar(50)  not null
    );

    -- Filling data
    INSERT INTO my_table (description)
    VALUES
    ('Option A'), ('Option B'), ('Option C'), ('Option D'), ('Option E'),
    ('Option F'), ('Option G'), ('Option H'), ('Option I'), ('Option J');

    SELECT * FROM my_table;

text
id  | description
----+------------
1   | Option A
2   | Option B
3   | Option C
4   | Option D
5   | Option E
6   | Option F
7   | Option G
8   | Option H
9   | Option I
10  | Option J

Accidental deletion:

sql
    DELETE FROM my_table WHERE id IN (6,7,8);

    SELECT * FROM my_table;

text
id  | description
----+------------
1   | Option A
2   | Option B
3   | Option C
4   | Option D
5   | Option E
9   | Option I
10  | Option J

In order to reinsert those rows back into the table, I tried to run the following instruction, explicitly defining values for the "id" column, but it raised an exception:

sql
    INSERT INTO my_table (id, description)
    VALUES
    (6, 'Option F'), (7, 'Option G'), (8, 'Option H');

text
[428C9] ERROR: cannot insert a non-DEFAULT value into column "id" Detail: Column "id" is an identity column defined as GENERATED ALWAYS. Hint: Use OVERRIDING SYSTEM VALUE to override.

We have 3rd party tool which is deleting data via below parametrize query:

delete from [sap].[PRCD_ELEMENTS]
where [rowid] = @P1

Table contains around 3 billions of records and is partitioned by different nvarchar(10) column. We have non clustered index on rowid (rowid is not a part of PK/clustered index). We could have 200 000 deletes per minute which means above query will be executed in one sessions with different rowid in a loop. I am thinking to speed it up and create instead of delete trigger which will be collecting deleted records with partitioned column. Also create job which will be executed every 1 minute which will be deleting collected records via join with new table with rowid and partition column. Something like this:

delete prcd
from [sap].[PRCD_ELEMENTS] prcd inner join 
dbo.newTableWithDeleteTriggerValues d on prcd.rowid = d.rowid and prcd.partitioned_column = d.partitioned_column

The question is what is the performance of instead of delete trigger and will handle this kind of problem.

In order to assess options of implementing indexed views, I want to check the usage of DML operations of the tables. The database is new to me as well as the relevant business knowledge, so I just have no idea. Is there a way to quickly collect a statistic within couple of days? I am using Standard Edition.

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log upon execution of the function with SELECT fnct_clear_temp_fields();. When I set log_statement = 'all', and execute the function I can see SELECT fnct_clear_temp_fields(); in the log, but not the underlying UPDATE commands. Is there a way to have the UPDATE commands appear in the log as well ? For info, here is the function: CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$ DECLARE --Put into a cursor a view dynamically listing all user-defined fields beginning with 'temp_' dataset_1 CURSOR FOR SELECT column_name, table_name FROM information_schema.tables NATURAL JOIN information_schema.columns WHERE table_schema='public' AND table_type='BASE TABLE' AND column_name ~ '^temp_' ORDER BY table_name,column_name; --Record variable to go through each line of the view above dataset_1_row RECORD; BEGIN OPEN dataset_1; --Open the cursor FETCH dataset_1 INTO dataset_1_row; --first row of the view WHILE FOUND LOOP RAISE NOTICE 'Table: %, Column: %', dataset_1_row.table_name,dataset_1_row.column_name; --Set to NULL the contents of the current 'temp_' column EXECUTE 'UPDATE '||dataset_1_row.table_name||' SET '||dataset_1_row.column_name||'=NULL WHERE '||dataset_1_row.column_name||' IS NOT NULL'; FETCH dataset_1 INTO dataset_1_row; --next row please. END LOOP; --while end CLOSE dataset_1; RETURN; END; $$ LANGUAGE plpgsql;

I program, administer, and support an ERP application. One of my more common tasks is to understand where data originates, finding the link between a particular report field and its data entry field in the application. To do this, I have to manually examine the DDL SQL of one or more views in order to understand the table columns, in order to match one view's table columns with another view's table columns. This manual task might require examination of four or five abstraction layers. Is there a faster way to examine the metadata to find how a view column is calculated from its base table column? Is there a way to examine Oracle's execution plan to see the rewritten query, skipping the manual inspection of the intermediate views?

Consider the following scenario: 1. 10,000 users try to create a profile on our application concurrently at the same time and the data has to be inserted into the table, userProfile. 2. The SP that handles this insertion takes half a second to execute. From my understanding of locking and transactions, this table will be locked for every insert. That means by the time the last user's profile is created it'll take a while. How does SQL Server handle concurrent updates and locking? I would appreciate any insights on this. Thank you!

I have an Oracle Database which includes many users and some of them have DBA roles. The thing is, DBA's shouldn't delete any records from SYS.AUD$ table. Is there a way that i can achieve this?

I'm trying to: - create an empty copy of a table - insert all the data from the original to the copy query I'm using is: CREATE TABLE my_schema.account_copy_like LIKE my_schema.account; -- To inherit all table definitions INSERT INTO my_schema.account_copy_like SELECT * FROM my_schema.account; -- Copying data from another table The issue here is that the INSERT INTO... SLEECT * FROM is not copying as expected, there are some discrepancies on the table information as you can compare on the table size and rows, I was wondering why. It seems that in the copy table it has more rows than the original but it's size is lower?

After some discussion on that topic I can assume that there is a quite frustrating fact about MySQL InnoDB: It does not support (atomic) transactions when it comes to DML. If you have a database migration with data there is a fairly easy solution to make it either completely fail or finish successfully. START TRANSACTION; INSERT INTO orders(orderNumber,orderDate) VALUES (1,'2020-05-31'); INSERT INTO orders(orderNumber,orderDate) VALUES (1,'2020-05-31'); COMMIT; A transaction is an atomic unit of database operations against the data in one or more databases. Unfortunately this is not true for the following: START TRANSACTION; CREATE TABLE Persons ( PersonID int, LastName varchar(255),FirstName varchar(255)); CREATE TABLE Ducks ( DuckID int, DuckName varchar(255)); CREATE INDEX duckname_index ON Ducks (DuckName varchar(255)); COMMIT; Each of that statements will create an implicit commit, so if the migration fails in between your MySQL database is broke and half migrated. From the docs: > Some statements cannot be rolled back. In general, these include data > definition language (DDL) statements, such as those that create or > drop databases, those that create, drop, or alter tables or stored > routines.You should design your transactions not to include such > statements. If you issue a statement early in a transaction that > cannot be rolled back, and then another statement later fails, the > full effect of the transaction cannot be rolled back in such cases by > issuing a ROLLBACK statement. As we have to implement a custom migration system for a certain software we are wondering now how this could be solved? How does e.g. Symfony (https://symfony.com/) Doctrine (https://www.doctrine-project.org/) solve that internally ? Ideas: 1. Solve it on CI/CD level and restore the old database if some error occurs? Cons: Sounds really clumsy. 2. Only allow Migrations with exactly one DML statement and strictly seperate DML and DDL migrations. Cons: You will have 10 or maybe hundreds of migration files per production deployment. Still I hope there is a better way? What is the best practical solution to that problem - if any?

I would like to understand the difference between a table and a View in SQL (Ms Access, if it makes any difference). So I know Create View command can be used to "pull" all fields and records from various tables in a database and present all this data together in one big table. But I don't understand what kind of object View is meant to be, if it isn't a table with data types? And what sort of DML and DDL commands can be executed on View? For example, can commands like DELETE FROM, UPDATE, etc be run on it?

I have created a postgres 12 instance and executed the below command sequence: psql --host localhost -U postgres -c "CREATE DATABASE mydb" psql --host localhost -U postgres -d mydb -f /tmp/createdb.sql The need is to create tables with ddluser and the dmluser can manipulate(insert/select/delete) the contents of the tables that ddluser will create. The contents of /tmp/createdb.sql are the following: DROP USER IF EXISTS ddluser; DROP USER IF EXISTS dmluser; DROP ROLE IF EXISTS ddlrole; DROP ROLE IF EXISTS dmlrole; CREATE USER ddluser WITH PASSWORD '1234'; CREATE USER dmluser WITH PASSWORD '1234'; CREATE SCHEMA myschema; REVOKE CREATE ON SCHEMA myschema FROM PUBLIC; REVOKE ALL ON DATABASE mydb FROM PUBLIC; CREATE ROLE ddlrole; GRANT CONNECT ON DATABASE mydb TO ddlrole; GRANT USAGE, CREATE ON SCHEMA myschema TO ddlrole; REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema FROM ddlrole; GRANT ddlrole TO ddluser; CREATE ROLE dmlrole; GRANT CONNECT ON DATABASE mydb TO dmlrole; GRANT USAGE ON SCHEMA myschema TO dmlrole; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO dmlrole; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dmlrole; GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO dmlrole; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO dmlrole; GRANT dmlrole TO dmluser; The table I create for example is the below: CREATE TABLE myschema.accounts ( user_id serial PRIMARY KEY, username VARCHAR ( 50 ) UNIQUE NOT NULL, password VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP ); Output is: mydb=> select * from myschema.accounts; ERROR: permission denied for table accounts mydb=> exit

I have 25 tables I refresh in one transaction. Few of them refresh much longer than others. I wonder would it possible to refresh "short-timers" in parallel of "long-timers" in same one transaction. One transaction is just for convenience to rollback changes on all tables in case of failure. Not sure yet if I want to achieve something contradictory to "transaction" concept.

First time posting but have been a reader for over 10 years. I have a need to show when a loan was paid off regardless of the rolling balance from refinancing/taking out additional loans. In the other column I want to show pay down balance like 950.00, 850.00, 800.00, 720.00 until I get to zero balance for the LOAN_ID 215, After which I want to then apply remaining payments to LOAN_ID 431 I'm using SQL Server 2005/2008. Here is my table: CUST_ID LOAN_ID PMNT_ID PMNT_AMT PMNT_DT LOAN_AMT LOAN_FUND_DT PMNT_RUN_TOT 1155 215 100 100.00 02/15/2015 1050.00 01/15/2015 100.00 1155 215 101 100.00 03/15/2015 1050.00 01/15/2015 200.00 1155 215 102 50.00 03/31/2015 1050.00 01/15/2015 250.00 1155 215 103 90.00 04/15/2015 1050.00 01/15/2015 340.00 1155 215 104 150.00 04/25/2015 1050.00 01/15/2015 490.00 1155 215 105 120.00 05/15/2015 1050.00 01/15/2015 610.00 1155 431 106 100.00 05/25/2015 2100.00 05/20/2015 710.00 1155 431 107 100.00 06/15/2015 2100.00 05/20/2015 810.00 1155 431 108 100.00 06/30/2015 2100.00 05/20/2015 910.00 1155 431 109 100.00 07/15/2015 2100.00 05/20/2015 1010.00 1155 431 110 100.00 08/15/2015 2100.00 05/20/2015 1110.00 1155 431 111 100.00 09/15/2015 2100.00 05/20/2015 1210.00 1155 431 112 100.00 09/30/2015 2100.00 05/20/2015 1310.00 1155 431 113 100.00 10/15/2015 2100.00 05/20/2015 1410.00 The first loan was paid off with PMNT_ID 110 and 60.00 was rolled over into the pay down of 2100.00 loan. Here is how the output should look cust_id|loan_id | loan_amount | Loan Bal |paid_off_date ---------------------------------------------------- 1155 | 215| 1050 | 0.00 |2015-08-15 1155 | 431| 2100 | 1800.00 |null Any suggestions on how to show this in another column called "Bal_Pay_Down" ?

I have created a DML trigger (AFTER INSERT, UPDATE, DELETE) on a table Trigger's logic takes about 30 seconds to execute So if you change even 1 row, it takes ~ 30 seconds due to trigger execution Developer asked me "Is there a chance that the trigger could be a fire & forget action?" I said no, but is it really so ? *Question:* Can trigger be executed in "asynchronous" mode? Application updates couple of rows in few ms, and thinks that transaction is completed, and then trigger is silently executed under the hood ? I understand that this actually does not look good from consistency point of view, but still, is it possible ?

What is the best practice / recommended way of using ROLLBACK operation, if any DML operation fails ? Any ROLLBACK, COMMIT mechanism ? FYI: I'm using InnoDB tables which complies with Atomicity.