What are the Magic tables or Logical Tables in SQL server and how to access Magic Tables?

Magic tables in SQL:

Magic tables are the logical tables in SQL server. There are two types of logical tables in SQL server:
  • Inserted
  • Deleted
 Magic tables or Logical tables are automatically created and managed by SQL Server internally. These tables hold the recently inserted, deleted and updated values during Insert, Update and Delete operations on a table. These tables are not visible and accessible directly. There are two methods to access these tables
  •  Using Triggers operation either After Trigger or Instead of trigger.
  • Without Triggers Using “OUTPUT” Clause 
Using Triggers:

Inserted Logical Table:

Inserted logical table holds the latest inserted value or updated value in the table.
Whenever we do insertion or updating the record in table in database, a table gets created automatically by the SQL server, named as INSERTED.


CREATE TABLE [dbo].[emp_details](
            [empid] [numeric](18, 0) NOT NULL,
            [empname] [varchar](100) NULL,
            [salary] [numeric](18, 0) NULL,)


Now Create a trigger


CREATE TRIGGER emp _Insertion
ON emp_details
FOR INSERT
AS
begin
SELECT * FROM INSERTED
SELECT * FROM DELETED
End


 Now Inert data in above table:


INSERT INTO  emp_details (empid, empname, salary) VALUES (201, XYZ ,1000)


 

Deleted Logical Table:

When we update the record in table then  two tables are created, one is INSERTED and another is called DELETED. Deleted table will hold the previous record after the updations and  Inserted table consists of the updated record.

CREATE TRIGGER emp_update ON emp_details
FOR update
AS
begin
SELECT * FROM INSERTED
SELECT * FROM DELETED
End


Update Record


update emp_details set empname='ABC' where empid=201




 Without Triggers Using Output Clause:

Output Clause  with Insert Command:


INSERT into emp_details  (  [EmpID],  EmpName, Salary  )
OUTPUT
 Inserted.[EmpID], Inserted.EmpName, Inserted.Salary
VALUES (208, 'Delton', 15000);


Result:

SQL: Introduction to SQL Server.

SQL: Introduction to SQL Server.

What is SQL?

The Structured Query Language (SQL) is the set of instructions used to interact with a relational database. SQL is used to query, insert, update and modify data. . Most relational databases support SQL. Whenever you interact with such a database, the software translates your into SQL statement that the database knows how to interpret. 

SQL History:
 SQL developed in the early 1970s at IBM by Raymond Boyce and Donald Chamberlin. In 1979, a company called Relational Software, which later became Oracle, saw the commercial potential of SQL and released its own modified version, named Oracle V2.  The current standard SQL version is monitored by the American National Standards Institute (ANSI).

Different Command Types in SQL:

DDL: Data definition Languages
DML: Data manipulation Language
DCL: Data control Language


TCL: Transaction Control Language

Kategori

Kategori