What is the Query of getting last 10 transaction Reports
(like insert, update, Delete Data from Tabele) ?
Answers were Sorted based on User's Feedback
Answer / sanjeev kumar
u hve to run query against transaction log.
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / praveen singh
CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);
drop table Employee_Test_Audit
CREATE TABLE Employee_Test_Audit
(
transid int NOT NULL IDENTITY (1,1) ,
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
GO
insert into Employee_Test values('Chris',1500);
CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]
FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
if update(Emp_Name)
set @audit_action='Updated Record -- After Update Trigger.';
if update(Emp_Sal)
set @audit_action='Updated Record -- After Update Trigger.';
insert into
Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER UPDATE Trigger fired.'
GO
update Employee_Test set Emp_Sal=1550 where Emp_ID=6
CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]
AFTER DELETE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=d.Emp_ID from deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
set @audit_action='Deleted -- After Delete Trigger.';
insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER DELETE TRIGGER fired.'
GO
CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
declare @emp_id int;
declare @emp_name varchar(100);
declare @emp_sal int;
select @emp_id=d.Emp_ID from deleted d;
select @emp_name=d.Emp_Name from deleted d;
select @emp_sal=d.Emp_Sal from deleted d;
BEGIN
if(@emp_sal>1200)
begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Test where Emp_ID=@emp_id;
COMMIT;
insert into
Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of
Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO
delete from Employee_Test where Emp_ID=1
select * from Employee_Test
select * from Employee_Test_Audit
ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete
alter table Employee_Test_Audit add column transid int NOT
NULL IDENTITY (1,1)
select top 10 * from Employee_Test_Audit order by transid
desc
Is This Answer Correct ? | 4 Yes | 3 No |
Answer / anand
select top 10(fieds_name) from <table_name> order by
(fields)name) desc.
Is This Answer Correct ? | 2 Yes | 14 No |
Answer / mona
select top 10 (field list) from (tablename) order by (your
autonumbering/identity column) desc
Is This Answer Correct ? | 5 Yes | 19 No |
4 Please write a querry to find repeated numbers in the following table. Table Name: Table1 Field1 10 15 20 15 10
tell me what are the steps you will take to improve performance of a poor performing query? : Sql server database administration
How to use go command in "sqlcmd"?
What is the use of Port no?
How do you implement session management in SQL Server mode?
Hi all, can any one please tell me the difference between sql server 2008 and orace 9i
Do you know what is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure?
Alternative way to DetDate() function?
What is the difference between a Local temporary table and a Global temporary table? How is each one used?
What is a dbms wizard?
what is the order of execution of where,having,group by in select stement
6 Answers IBM, Tanla Solutions,
What is meant by indexing?