SQL Trigger

Triggers in MSSQL are very useful but we need to take some precautions about it because of use of nested triggers the SQL Server only go up to 32 nested triggers. Triggers can be used in different situations like:

• Insert, Update, Delete  

Using the AFTER keyword that is default or using FOR keyword

Example using FOR and deleted :

CREATE TRIGGER Save_Deleted_records

ON dbo.able_Test

FOR DELETE

AS

INSERT deleted_records_mark

SELECT * FROM deleted

more on microsoft Web Site:http://msdn.microsoft.com/en-us/library/ms190739(SQL.90).aspx

Example using a AFTER Keyword

Create TRIGGER dbo.UpdateTrigger ON dbo.Table3 

AFTER INSERT AS 

 BEGIN 

   SET NOCOUNT ON; 

     declare @ticket  as varchar(8) 

     set @ticket = (select callid from inserted)

     update subset set uticketrem = @ticket where custtype<>'Facility' and callid=@ticket

END

• Instead of Insert, Update, Delete 

these means that you can do other operations like adding or deleting records


CREATE TRIGGER Example_Trigger on Table1
INSTEAD OF INSERT
AS
BEGIN
  -- Instead of inserting you can do other operations
  INSERT INTO Table45
       SELECT RowMatCol, Date_Doc
       FROM inserted
END


More on Microsoft Web Site here: http://technet.microsoft.com/en-us/library/ms175089.aspx

Happy Christmas and an Happy New Year 

Sponsored by IT Tech BuZ

Database Triggers

One more post about SQL Tips and Tricks that i think that have is own value. Today I write about Database Triggers, they are used for security reasons and audit operations, let's see the example.

Use Example_DB
go
Create TRIGGER [DBTRG_Test_Drop_Create]
ON DATABASE
FOR DDL_TABLE_VIEW_EVENTS
AS
DECLARE @data xml
DECLARE @cmd nvarchar(350)
DECLARE @logMsg nvarchar(400)

SET @data = eventdata()
SET @cmd = @data.value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(350)')
SET @logMsg = @cmd + ' (' + SYSTEM_USER + ' on ' + HOST_NAME() + ')'

RAISERROR (@logMsg, 10, 1) WITH LOG

-- To visualize the command uncomment the line bellow
-- select @cmd


-- Here we don't allow command that begin with "Create"
-- if the condition is true we do rollback

if left(@cmd,6)='Create'
rollback

-- Here we don't allow command that begin with "Drop"
-- if the condition is true we do rollback

if left(@cmd,4)='drop'
rollback

Note: We can create a table with type specified in variables and insert the attemps of create and drop objects.

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor