Sql Query For Shrinking Log File
sp_helpdb dbname
backup log dbname with truncate_only
dbcc shrinkfile(db_log,10)
Triggers
After lot of effort I have put some very basic triggers for everyone finding it
difficult with triggers
1)For Delete Example
This trigger will insert the deleted record from table into a new destination table.
CREATE TRIGGER BeforeDeleteCustIdeaMain
ON tablename for DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @Prism_DocId bigint
insert into destinationtable
select * from deleted
END
GO
2)After Insert Trigger
CREATE trigger [dbo].[AfterInsertCustIdeaMainUpdateDump]
on tablename
after insert
as begin
Declare @Prism_DocId bigint
set @Prism_DocId=(select Prism_DocId from inserted)
update destinationtable
set received=1,receiveddate=getdate()
where docid=@Prism_DocId
END
3)Third trigger For Update
If you wish to save the old and the updated value of particular column
this trigger would be helpfull.In this case it would insert the old MobileNo,New MobileNo,primary key and the date modified in new table.
ALTER TRIGGER [dbo].[updateDataEntry]
ON sourcetable
FOR UPDATE
AS
BEGIN
IF UPDATE(MobileNo)
BEGIN
DECLARE @OldMobileNo bigint,@UpdatedMobileNo bigint,@Id bigint
SELECT @OldMobileNo=b.MobileNo, @UpdatedMobileNo=a.MobileNo,
@Id=a.Id-- Get the Old and New values
FROM inserted a, deleted b
insert into destinationtable(DataEntryID,Edited,OldMobileNo,UpdatedMobileNo)
values(@Id,getdate(),@OldMobileNo,@UpdatedMobileNo)
END
SQl Query for listing all the objects owned by user other than Dbo
select s.name, u.name from sysusers u, sysobjects s
where s.uid=u.uid and s.uid!=1
Sql Query For Dropping User
sp_dropuser vodafonepost
