Wednesday, February 11, 2009

database trigger

CREATE TRIGGER ddl_trig_loginAW
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
PRINT 'Added trigger event to DDLServerTriggerData'
INSERT INTO [AdventureWorks].[dbo].[dbo.DDLServerTriggerData]
(DDLServerEvent) VALUES ( EVENTDATA())

IFEXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'safety')
DROPTRIGGER safety ON DATABASE;
GO
CREATETRIGGER safety
ONDATABASE
FORDROP_TABLE, ALTER_TABLE ,create_table
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK;
GO


DISABLETRIGGER safety ON DATABASE;
GO
Posted by at

3 comments:

  1. UnknownFebruary 17, 2009 at 12:18 AM

    thanx
    arun

    arunpanwar231@gmail.com

    ReplyDelete
  2. jayantMarch 1, 2009 at 9:34 PM

    alter PROCEDURE sp_decrypt_sp (@objectName varchar(50))
    AS
    DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
    declare @i int , @t bigint

    --get encrypted data
    SET @OrigSpText1=(SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
    SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' AS '
    EXECUTE (@OrigSpText2)

    SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
    SET @OrigSpText2='CREATE PROCEDURE '+ @objectName +' AS '

    --start counter
    SET @i=1
    --fill temporary variable
    SET @resultsp = (datalength(@OrigSpText1) / 2)

    --loop
    WHILE @i<=datalength(@OrigSpText1)/2
    BEGIN
    --reverse encryption (XOR original+bogus+bogus encrypted)
    SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
    (UNICODE(substring(@OrigSpText2, @i, 1)) ^
    UNICODE(substring(@OrigSpText3, @i, 1)))))
    SET @i=@i+1
    END
    --drop original SP
    --EXECUTE ('drop PROCEDURE '+ @objectName)
    --remove encryption
    --preserve case
    SET @resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', '')
    SET @resultsp=REPLACE((@resultsp),'With Encryption', '')
    SET @resultsp=REPLACE((@resultsp),'with encryption', '')
    IF CHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0
    SET @resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', '')
    --replace Stored procedure without enryption

    execute( @resultsp)

    print (@resultsp)
    GO

    ReplyDelete
  3. UnknownMarch 22, 2009 at 1:27 AM

    THis is nice to see your personal blog. BY take care.

    ReplyDelete

Subscribe to: Post Comments (Atom)