example of trigger

create trigger trig1
on database
FOR CREATe_table,alter_TABLE,drop_table
AS

PRINT 'U CANT CREATE TABLE'
rollback
---------------
create trigger [trig1]
on database
FOR CREATe_table,alter_TABLE,drop_table
AS
BEGIN tran
begin try
PRINT 'U CANT CREATE TABLE'
end TRY
BEGIN catch
PRINT 'U DONT HV PERMISSION TO CREATE TABLE'
rollback TRAN
END catch
GO
--------------

create trigger [trig1]
on database
FOR CREATe_table,alter_TABLE,drop_table
AS

begin tran
begin try
PRINT 'U CANT CREATE TABLE'
rollback tran
end try
begin catch
if @@error=3609
print 'u dnt hv permission to create table'
end catch
GO
------------------------------------------------
 create Trigger [Audit_History]
  ON DATABASE
  FOR DDL_DATABASE_LEVEL_EVENTS
  AS
  SET NOCOUNT ON

DECLARE @data xml
SET @data = EVENTDATA()

INSERT INTO dbo.DML_History(servername, databasename,SUser, eventtype,
objectname, objecttype, sqlcommand, spid, username, loginname,EventData)
VALUES(
@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
HOST_NAME(),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(5)'),
@data.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
@data
)
-----------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[DML_History](
    [LogId] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [varchar](100) NULL,
    [DatabaseName] [varchar](100) NULL,
    [SUser] [varchar](50) NULL,
    [EventType] [varchar](100) NULL,
    [ObjectName] [varchar](100) NULL,
    [ObjectType] [varchar](25) NULL,
    [SqlCommand] [varchar](max) NULL,
    [EventDate] [datetime] NOT NULL,
    [SPID] [varchar](5) NULL,
    [UserName] [varchar](50) NULL,
    [LoginName] [varchar](50) NULL,
    [EventData] [xml] NOT NULL,

------------------------------------------------------------------
DML TRIGGER
------------




CREATE TRIGGER  [dbo].[Trig_history_DML]
ON [dbo].[ssis] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
DECLARE @buffer TABLE (
        EventType nvarchar(30),
        Parameters int,
        EventInfo xml)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'

    DECLARE @operation  VARCHAR(10)
    SELECT @operation = (
    CASE
    WHEN EXISTS(SELECT * FROM INSERTED)AND EXISTS(SELECT * FROM DELETED)
                        THEN 'Updated'  -- Set Action to Updated.
    WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN 'Inserted'  -- Set Action to Insert.
    WHEN EXISTS(SELECT * FROM DELETED)
                        THEN 'Deleted'  -- Set Action to Deleted.
    ELSE NULL -- Skip. It may have been a "failed delete". 
   END)
-- Capturing Delete Operation
    IF @operation = 'Deleted'

        BEGIN
            INSERT INTO dbo.Test_History(Server_Name,Database_Name,S_User,Application_Name,Operation,Event_Date,SPID,User_Name,Login_Name,EventData)
            SELECT @@SERVERNAME,DB_NAME(),HOST_NAME(),APP_NAME(),'DELETE',GETDATE(),@@SPID,SUSER_NAME(),SUSER_SNAME(),(SELECT  EventInfo FROM @buffer)
            FROM deleted
        END
ELSE
        BEGIN

    IF @operation = 'Inserted'

        BEGIN
             INSERT INTO dbo.Test_History(Server_Name,Database_Name,S_User,Application_Name,Operation,Event_Date,SPID,User_Name,Login_Name,EventData)
     SELECT @@SERVERNAME,DB_NAME(),HOST_NAME(),APP_NAME(),'INSERT',GETDATE(),@@SPID,SUSER_NAME(),SUSER_SNAME(),(SELECT  EventInfo FROM @buffer)
      FROM inserted
        END

-- Capture Update Operation
ELSE

        BEGIN
            INSERT INTO dbo.Test_History(Server_Name,Database_Name,S_User,Application_Name,Operation,Event_Date,SPID,User_Name,Login_Name,EventData)
    SELECT @@SERVERNAME,DB_NAME(),HOST_NAME(),APP_NAME(),'UPDATE',GETDATE(),@@SPID,SUSER_NAME(),SUSER_SNAME(),(SELECT  EventInfo FROM @buffer)
    FROM inserted

        END
END
GO




ALTER PROCEDURE Uams.AMS_UpdateAssetRequestFromDraft(@DraftAssetIds varchar(max))
As
Begin
    declare @Query nvarchar(max)
    set @Query='update Uams.AMS_RequisitionDraftAsset set Status=''Draft'' where Draft_Asset_ID in('+@DraftAssetIds+')'
    execute sp_executesql  @Query
End