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 |
What stored by the master? : sql server database administration
Why use view instead of a table?
How to configure odbc dsn with different port numbers?
Mention the differences between substr and charindex in sql server.
What are the steps you will take to improve performance of a poor performing query?
if a parameter is not send to a stored procedure what type of identifier is to be used in Stp and if that parameter is not feed to the a query inside the Stp how to validate with out useing IF condition
What does it mean to manipulate data?
What are the different editions available in sql server 2000?
What is an extended Stored Procedure?
How do I connect to sql server database?
How secure is sql server database?
How to override dml statements with triggers?