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

SQL Function to Concatenate DateTime

Today I bring to you a useful SQL function to get a string like YYYYMMDDHHMMSS (example for 30-10-2009  20:01:01 will be 20091030200101).

Use YourDatabase
Create function [dbo].[F_Datetime]()
returns varchar(50)
as
begin
declare @datanext varchar(50)
select @datanext =
(select (substring ((select convert(varchar(10),getdate(),103)),7,4)))+  -- Year
(select (substring ((select convert(varchar(10),getdate(),103)),4,2)))+ -- Month
((select (substring ((select convert(varchar(10),getdate(),103)),1,2)))+ -- Day
replace((select convert(varchar(8),getdate(),108) ),':','')) -- hour and minutes HHMMSS

return @datanext
end  



Then you can use like these:

Use YourDatabase
Go
Select  [dbo].[F_Datetime]()


Then your result looks like these:


20091030203830

More on Crystal Reports SQL Expressions and WhilePrintingRecords

I decided to talk about more on Crystal Reports and about SQL expressions fields that can be tricky I have made some screenshoots that can help YOU. See below

Let's see Closer the intruction remenber on

Convert text to varchar

• The formula bellow sums field1 group by field2

Whileprintingrecords;
sum ({table1.field1},{table1.field2})

• Sums two fields in the bellow example
  

WhilePrintingRecords;
({file.Qty1} + {file.Qty2})

more on these useful  Link


Related Posts:

• Crystal SQL Expressions
• Crystal Reports While Printing