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

Viewing Jobs running, using a MSSQL Statement

Sometimes we need to view which jobs are running in different contexts like administrative proposes or for data update and we need to see what´s is happening.

USE msdb ;


GO


EXEC dbo.sp_help_job;



Go


USE msdb ;



GO


EXEC dbo.sp_help_job



@job_name=N'MaintenancePlanBackup',   -- Specify the name of your job

@job_aspect=N 'ALL';


GO
/*
Using Multiserver
*/


USE msdb ;

GO


EXEC dbo.sp_help_job

@job_type=N'MULTI-SERVER',
@owner_login_name=N'sa', @enabled=1 , @execution_status=1;


GO


View this link for more information
http://msdn.microsoft.com/en-us/library/ms186722.aspx



/*
View Activity Jobs
*/


USE msdb

GO


EXEC  dbo.sp_help_jobactivity ;


GO


Related Posts:

• Error Handling SP 
• SQL Date Manipulation Functions and Examples
• Database Triggers
• Create SP with Cursor
• Stored Procedure to Copy Files

Error Handling SP with Transaction

Let´s see best practices implemented on creating Stored procedures. So when we create the procedure we must implement some kind of error handling to control. So let´s see what is the syntax:

Create Procedure Nametheprocedure
-- Parameters input or output
As
BEGIN TRY
Begin Transaction
-- Commands
commit transaction -- If it runs without error commit the transaction

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER(), ERROR_MESSAGE()

Rollback
END CATCH

GO

So these is the syntax and bellow is an example



USE AdventrureWorks
go
CREATE PROCEDURE Production.AddReview
@ProductID int,
@ReviewerName nvarchar(50),
@EmailAddress nvarchar(50),
@Rating int,
@Comments nvarchar(3850)
as


BEGIN TRY
Begin transaction;
INSERT Production.ProductReview (ProductID, ReviewerName, EmailAddress, Rating, Comments)
VALUES (@ProductID, @ReviewerName, @EmailAddress, @Rating, @Comments)
commit transaction;
END TRY

BEGIN CATCH

SELECT ERROR_NUMBER(), ERROR_MESSAGE()
rollback transaction;
END CATCH

GO

-- Test stored procedure with error handling
EXECUTE Production.AddReview 715, 'Doug', 'doug@siteexample.com', 10, 'Great product!'
GO

See you Soon :)

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

SQL Date Manipulation Functions and Examples

Date manipulation in SQL Server there are several functions, lets see same examples of that:

-- Example 1

select getdate()

these function returns current date time like 2009-07-26 14:55:03.210

-- Example 2

select GETDATE()+60

On these example we add 60 days and the result is 2009-09-24 14:56:40.210

-- Example 3

select DATEADD(M,2,getdate()) -- M month, y year, d day

The DateAdd function adds months, years or days to date supplied

So you can see that adding 2 month to the current date and the result is 2009-09-26 14:57:48.117

see the differente results between example 2 and 3 ( 2 days), because in the example 2 we add 60 days not 2 month.

-- Example 4

select DATEDIFF(DAY,(DATEADD(M,2,getdate())),(GETDATE()+60))

The function DateDiff calculates the difference between to dates and returns int, using the examples that you saw the result is -2

-- Example 5

SET DATEFORMAT dmy;

GO

DECLARE @datevar datetime

Set @datevar=GETDATE()

select @datevar

On this example you will saw how to format the date to British format

-- Example 6

select substring((convert(varchar(10),@datevar,127)),1,10)

result 2009-07-26

Converting date type to output varchar(10) using parameter 127 ( yyyy-mm-dd)

-- Example 7

select substring((convert(varchar(10),@datevar,103)),1,10)

result 26/07/2009

Converting date type to output varchar(10) using parameter 103 ( yyyy-mm-dd)

See more about convert function in http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

See more on Date on http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx

Post that can help you avoid some problems:

• How to solve problems with Identity 
• About SQL Bulk Insert
• Create Stored Procedure with Cursor

Anniversary of IT Tech Buz EN Technical

I would like to thanks our readers, sponsors and My wife and son for one more year with in a time of change.I see that our visitors are growing on a daily basis for me it's really a pleasure.
To improve I will need some feedback, only 2 questions !!!

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

Stored Procedure to Copy Files

Stored procedure to help you out to copy files see the code bellow

USE [aspnetdb] -- Replace with your DB

GO

/****** Object: StoredProcedure [dbo].[usp_copyfile] Script Date: 04/21/2009 09:22:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create procedure [dbo].[usp_copyfile]

@msg as varchar(200),

@input as varchar(100)

,@retorno as varchar(200) output

as

Begin

declare @drive as varchar(100)

declare @drive_dest as varchar(100)

declare @path as varchar(800)

declare @path_dest as varchar(800)

declare @cmd as varchar(800)

declare @ext as char(3)

set @path_dest='\Dest_Path\'

-- Para Exemplo, não esquecer barra de directorio final

-- Do not forget of blackslash

--

set @path='\PROGRA~1\DATA\'

set @drive='c:'

set @drive_dest='d:'

set @cmd='copy '+@drive+@path+@msg+@ext

set @cmd=@cmd+@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))

+(select cast(month(getdate()) as varchar(2)))

+(select cast(year(getdate()) as varchar(4)))

+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

-- Para Testar Caminhos e Visualizar

-- For testing paths and to show

-- select @cmd

exec master..xp_cmdshell @cmd ,no_output

Set @retorno=@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))

+(select cast(month(getdate()) as varchar(2)))

+(select cast(year(getdate()) as varchar(4)))

+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

end

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

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

Create SP with Cursor

These is my first post in the New Year of 2009, because i had lot's of thing in my professional side, you already notice the change in the logo and in the name of the Blog, i had register the domain www.ITTechBuZ.com e novo logo.
The logo, pretends to give the communication as the major role between Business and IT.
This blog continues to post for Tech people. Today I post one more of the saga of SQL Tip and Tricks about a Store procedure (SP) with a cursor inside to update maintance contracts

--
-- Creates SP com o nome sp_update_onsite
--
create procedure [dbo].[sp_update_onsite]
as Begin


--
-- Inserts ticket in a table when the status is 'Closed'
-- I use an Insert with a Select
--



INSERT INTO [HEAT].[dbo].[Tickets] ([CallId] ,[CallType] ,[CriData] ,[Valor] ,[Saldo] ,[UIDMANUT])


select d.callid,c.calltype,c.closeddate, d.horas_gastas, d.horas_saldo
,d.uidmanut,c.custid
from detail d
inner join calllog c on c.callid=d.callid
inner join subset s on s.callid=c.callid
inner join config cfg on cfg.u_idreg=d.uidmanut
where (c.calltype='Onsite' or c.calltype='Packs') and c.callstatus='Closed' and c.actualiza<>'SIM'
and c.callid not in (select callid from tickets )

--
--
-- Cursor used to update
--

-- Declares the Cursor Cursor_Tickets
-- and Select where it runs the Cursor

DECLARE Cursor_tickets CURSOR for
SELECT t.CallId,c.custid,t.Valor,t.UIDMANUT
from tickets t
inner join calllog c on c.callid=t.callid
where t.[CallType]='Onsite' and c.actualiza <>'SIM'

--
-- Declares the variables to pass througth
--

Declare @Callid varchar(8)
declare @custid varchar(50)
declare @valor decimal(17,2)
declare @uidmanut varchar(25)

set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''


Open Cursor_tickets /* abrir o cursor */

fetch next from Cursor_tickets
into @callid,@custid,@valor,@uidmanut
while @@fetch_Status=0
begin

--- Updates hours spent
--- select @valor,@custid,@uidmanut (for Testing purposes)

update config set horas_gastas=isnull(@valor,0)+(isnull(horas_gastas,0)) ,horas_saldo=isnull(horas_saldo,0)-isnull(@valor,0)
where u_idreg=@uidmanut and custid=@custid

set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''

fetch next from Cursor_Tickets
into @callid,@custid,@valor,@uidmanut
end
close Cursor_tickets
Deallocate Cursor_tickets

--
-- In the end updates the table for not processing agains
--

update calllog set actualiza='SIM' where callid in (select callid from tickets) and actualiza<>'SIM'


end