Tempopary tables are created at runtimes and ability to perform all the operation like the normal table. These tables have limited scope. This table is stored in tempdb.
There are two types of temporary table:
Local Temporary table: This table is available only for the current connection. It is automatic deleted once disconnects from instance. This table is createdby putting single #(Hash) before the name of the table.
CREATE TABLE #EeLocTemp
Global Temporary table: This table is available for all the use that are connected with the server. It is automatic deleted once disconnects from all the userside. This table is createdby putting double ##(Hash) before the name of the table.
CREATE TABLE ##EeGloTemp
These tables hold the recent data from the insert, delete update statement. There are two tyes of magic table in sql inserted and deleted.
As the name suggest insert record is stored in inserted magic table and delete record is stored in deleted magic table.
Update record is stored in Inserted table. There is no separate table for updated data.
In previous version of SQL this table is only used in trigger but now they can be used in non-trigger statement.
Create your table:
Create TABLE StdInfo
Create audit table
Create TABLE StuHis
NAME VARCHAR (100),
Create the trigger for your info table
Create trigger infotrigger on StdInfo
For insert AS
Declare @Roll int;
Declare @NAME varchar(100);
Declare @message_audit varchar(100);
Select @Roll=I.Roll from inserted I
SELECT @NAME= I.NAME FROM INSERTED I
SET @message_audit='inserted successfully, your action is
insert into StuHis
PRINT 'For any error please connect with your
Injection in SQL
When malicious code inserted in place of some valid text required from the user side. Then it is called as Injection in SQL. When we submit the code then it will process the malicious code.
if supose user inserted the code in place of name
@Name : A;Drop table Stu_info,
; means that new query executed
Web developer uses the blacklist of words to avoid injection. This method not in use because drop and delete are some common word which is used in common English language.
There are many ways by which we can prevent the sql injection
So we need to use SQL parameter to prevent injection:
SQL parameter is the values that are added to an SQL query at execution time in a controlled manner.
User_Id = getRequestString('UserId');
InjSQL = 'SELECT * FROM Users WHERE UserId = @0';
this @0 ensure that the value which may be inserted is userid only.
2nd method is stored procedure. In method we need to use sp_executesql. It can accept parameterized variables only
CREATE PROCEDURE GetCustomerDetails
DECLARE @SQL NVARCHAR(2000)
SET @SQL =
'SELECT ContactName FROM Customers WHERE Id = @Id'
sp_executesql @SQL, N'@CustomerId CHAR(5)', @CustomerId = @CustId
sp_executesql accept only parametric data so it any malicious code inserted by the user then it treated as text for search item not the part of the query.
Validate all string entered by the user that it can not contain any character value.