Trigger

Triggering

---

Concept
Trigger that will run when someone updates the SalaryGrade within the specified table automatically the trigger will capture the previous salarygrade and insert into another table along with New and previous salarygrade..

--- Table 1 : Employee
Create Table Employee (
EmployeeID int not null
Constraint pk_Employee_EmployeeID primary key,
EmployeeName varchar(10) not null,
SalaryGrade varchar(10) not null,
Slary smallmoney not null
)
-------------------------------------------------------------
--- Table 2 : EmployeeChanges
Create Table EmployeeChanges (
ChangeID int identity(1,1) not null
Constraint pk_EmployeeChanges_ChangeID primary key,
ChangeDateTime datetime not null,
EmployeeName varchar(10) not null,
OldSalaryGrade varchar(10) not null,
NewSalaryGrade varchar(10) not null,
)
--------------------------------------------------------------
--- Before Update Insert to Another Table
CREATE Trigger tr_Update_RecordSalaryGradeChanges
On Employee
For Update As
declare @rowcount as int
declare @ChangeDateTime as datetime
declare @EmployeeName as varchar(10)
declare @OldSalaryGrade as smallmoney
declare @NewSalarygrade as smallmoney
If @@RowCount > 0
Begin
If update (Salarygrade)
Begin
begin transaction
select @ChangeDateTime = getdate(),
@EmployeeName = Inserted.EmployeeName,
@OldSalaryGrade = Deleted.Salarygrade,
@NewSalaryGrade = Inserted.Salarygrade
from Inserted inner join Deleted
on Inserted.EmployeeID = Deleted.EmployeeID
insert EmployeeChanges(ChangeDateTime, EmployeeName, OldSalaryGrade, NewSalarygrade)
values (@ChangeDateTime, @EmployeeName, @OldSalaryGrade, @NewSalarygrade)
set @rowcount = @@rowcount
if @@rowcount = 1
Begin
Commit transaction
End
Else
Begin
Rollback transaction
RaisError('Error recording to change table, contact admin', 16, 1)
End
End
End
Return
--- drop trigger tr_Update_RecordDeliveryChargeChanges
----------------------------------------------------------------
--- For Multiple Records Update in Employee Tables
Alter Trigger tr_Update_RecordSalaryGradeChanges
On Employee
For Update As

If update (salarygrade)
Begin

Insert EmployeeChanges(ChangeDateTime, EmployeeName, OldSalaryGrade, NewSalaryGrade)
Select getdate(), Inserted.EmployeeName,Deleted.SalaryGrade,Inserted.SalaryGrade
From Inserted inner join Deleted
on Inserted.EmployeeID = Deleted.EmployeeID

If @@error <> 0
Begin
RaisError('Error recording to change table, contact admin', 16, 1)
End
End
----------------------------------------------------------
--- For mail send while Update in Employee Table
/*
To ensure before this trigger configure your database mail.
for more information please visit db mail configuration Page :
http://sqlservertechnics.blogspot.com/2010/03/db-mail-configuration.html
*/
ALTER TRIGGER EmployeeUpdateMail
-- and the table is CustomerInfo
ON Employee
-- trigger is fired when an update is made for the table
FOR UPDATE
AS
-- holds the EmployeeID so we know which Customer was updated
declare @EmployeeID varchar(10)
-- holds the body of the email
declare @body varchar(2000)
-- holds the old customer name which has been changed
declare @EmployeeName varchar(10)
-- holds the new customer name
declare @EmployeeNewName varchar(10)
-- gets the previous customer first name that was deleted
SELECT @EmployeeID = EmployeeID,
@EmployeeName = d.EmployeeName
FROM deleted d
-- gets the new customer first name
SELECT @EmployeeNewName = EmployeeName
FROM inserted

SET @body = 'Employee with ID= ' + @EmployeeID + ' has been updated
with previous Employee Name is : ' + @EmployeeName + 'and the
New Employee Name is : '+ @EmployeeNewName
--- xp_sendmail is the extended sproc used to send the mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Email',
@recipients = 'adscxdasf@dsfasdf.com',
@body = @body,
@subject = 'Employee Information Updated'
GO
-----------------------------------------------------------
-- Workouts
Select * from dbo.Employee
Select * from dbo.EmployeeChanges
--- delete from dbo.EmployeeChanges

update Employee set salarygrade='FinalMail'
where EmployeeID in (1,2)

update Employee set EMPLOYEENAME='FinalMail5'
where EmployeeID in (1)
-----------------------------------------------------------

Error and Troubleshoot

Error and Troubleshoot in SQL server 2005

---

-- Errors and Error methods
BEGIN TRY
-- SELECT 1/0--Evergreen divide by zero example!
select FirstName/100 from customer
END TRY

BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
SELECT ERROR_NUMBER()
SELECT ERROR_SEVERITY()
SELECT ERROR_STATE()
SELECT ERROR_PROCEDURE()
SELECT ERROR_LINE()
SELECT ERROR_MESSAGE()
RETURN
END CATCH;


-- Error Query

select distinct e.department_id + ' - ' +d.department_name as [Department Name]
from employee e
left outer join Department as d on d.department_id=e.department_id

/*-- Error Message
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
*/

-- Correct Query
select distinct
CAST(e.department_id AS NVARCHAR) + ' - ' +d.department_name as [Department Name]
from employee e
left outer join Department as d on d.department_id=e.department_id

---

_______________________________________________________________

SQL Server Back Up and History

SQL Server Back Up and History

-- BACKUP SERVICE MASTER KEY
BACKUP SERVICE MASTER KEY TO FILE = 'C:\backup\servicemasterkey_backup.key'
ENCRYPTION BY PASSWORD = 'password@5'

-- RESTORE SERVICE MASTER KEY
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\backup\servicemasterkey_backup.key'
DECRYPTION BY PASSWORD = 'password@5'
FORCE

-- BACKUP MASTER KEY
BACKUP MASTER KEY TO FILE = 'C:\backup\masterkey_backup.key'
ENCRYPTION BY PASSWORD = 'password@5'

-- RESTORE MASTER KEY
RESTORE MASTER KEY FROM FILE = 'C:\backup\masterkey_backup.key'
DECRYPTION BY PASSWORD = 'password@5'
ENCRYPTION BY PASSWORD = 'password@5'
FORCE

-- To Back up Database
BACKUP DATABASE [Employee] TO DISK = N'C:\Backup\Employee.bak'
WITH NOFORMAT, NOINIT, NAME = N'Employee-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- To Restore Databse
RESTORE DATABASE [Employee] FROM DISK = N'C:\Backup\Employee.bak'
WITH FILE = 1,
MOVE N'Employee_log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Employee_log.LDF',
NOUNLOAD, REPLACE, STATS = 10
GO

--- SQL Server Script to Retrievw Job History
select job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
) t
) t
order by job_name, run_datetime

-- or

SELECT sj.name,
sja.run_requested_date,
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs sj
ON sja.job_id = sj.job_id
WHERE sja.run_requested_date IS NOT NULL
ORDER BY sja.run_requested_date desc


-- To Retrievw Path and Size of the Databas Backup
SELECT top 10 s.database_name,
m.physical_device_name,
s.backup_size,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
-- Optional Condition
-- WHERE s.database_name = 'BackupReport'
-- ORDER BY database_name, backup_start_date, backup_finish_date

-- To check SQL Server FULL database backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server Transaction Log backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -344, GETDATE()) AND GETDATE()
AND Type = 'L'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server Differential Backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'I'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server File\File Group Backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'F'
ORDER BY backup_set_id DESC
GO

Operator in SQL Server 2005

--*************************************--
-- ALL About Operator --
--*************************************--

-- ADD or Create New Operator
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'Dbmail',
@enabled=1,
@pager_days=0,
@email_address=N'testmailfromdb@domin.com',
@pager_address=N'',
@netsend_address=N''
GO

-- UPDATE operator
USE [msdb]
GO
EXECUTE msdb.dbo.sp_update_operator @name = 'Dbmail',
@email_address = 'testmailfromdb2@domin.com'
GO

-- DELETE operator with the same name.
EXECUTE msdb.dbo.sp_delete_operator @name = N'Dbmail'

-- To DISABLE the operator
EXEC msdb.dbo.sp_update_operator @name=N'Dbmail',
@enabled=0

-- To ENABLE the operator
EXEC msdb.dbo.sp_update_operator @name=N'Dbmail',
@enabled=1

DB Mail Configuration

Database Mail Configuration in SQL Server 2005

-- Enabling Database Mail
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

-- Checking Database Mail is running or not
EXEC msdb.dbo.sysmail_help_status_sp;

-- Starting Database Mail
EXEC msdb.dbo.sysmail_start_sp;

-- Stopping Database Mail
EXEC msdb.dbo.sysmail_stop_sp;

-- Checking Mail Queue Status
-- The state of mail queue should be 'RECEIVES_OCCURRING'
-- The column length gives the number of mails in the queue
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

-- Adding Database Mail Account
-- Add valid smtp mailserver_name
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Production Database Administration Profile',
@description = 'Administrative e-mail account',
@email_address = 'dfsafasfddffasd@yahoo.com',
@display_name = 'Automated Admin Mailer',
@mailserver_name = 'mail.yahoo.com' ;

-- Adding Database Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Production Database Administration Profile',
@description = 'Administrative mail profile' ;

-- Adding Database Mail Account to Profile
/* Add unique integer Sequence number for respective account in a profile. Mail is sent through the account based on the order of sequence number. */
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Production Database Administration Profile',
@account_name = 'Production Database Administration Account',
@sequence_number = 1 ;

-- Grant access to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Production Database Administration Profile',
@principal_name = 'public',
@is_default = 1

-- Listing all Database Mail Accounts
EXECUTE msdb.dbo.sysmail_help_account_sp ;

-- Listing all Database Mail Profiles
EXECUTE msdb.dbo.sysmail_help_profile_sp;

-- Listing all Database Mail Profiles with their respective Accounts
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

--To find out the profile name
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]

-- Deleting Database Mail Account
-- Pass Account name or account id as the parameter
EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_name = 'Production Database Administration Account' ;

EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_id = 1;

-- Deleting Database Mail Profile
-- Pass Profile name or profile id as the parameter
EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'Production Database Administration Profile' ;

EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_id = 1;

-- Deleting Database Mail Account from Profile
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'Production Database Administration Profile',
@account_name = 'Production Database Administration Account' ;

-- Updating Database Mail Account
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_id = 1,
@account_name = 'DBA',
@email_address = 'dsafffffewew@yahoo.com',
@display_name = 'Admin';

-- Updating Database Mail Account
EXECUTE msdb.dbo.sysmail_update_profile_sp
@profile_id = 1,
@profile_name = 'DBA'

-- Sending Database Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'dafskjkj@yahoo.com',
@copy_recipients = 'Abcjkdsfah@yahoo.com',
@blind_copy_recipients ='dalasldkfa@yahoo.com',
@body_format = 'TEXT',
@body = 'Test Email',
@subject = 'Automated Test Mail';

-- Send file attachments
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'dsafsdfasdf@yahoo.com',
@file_attachments = 'D:\File1.txt;D:\File2.txt'
@body = 'Test File Attachments',
@subject = 'Files Attached';

-- Send mail with query results
/*Specify value 0 for @attach_query_result_as_file for including the contents in body and specify value 1 to send the results as attachment*/
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'asdfjjhdsaf@yahoo.com',
@query = 'SELECT COUNT(*) FROM msdb.dbo.DTA_input' ,
@subject = 'Imported Records Count',
@attach_query_result_as_file = 1 ;

-- Sending HTML Mail
DECLARE @tableHTML VARCHAR(MAX) ;

SET @tableHTML =
'

HTML Mail

' +
'' +
'' +
'

Table Header

' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients='fsdfgdsgds@yahoo.com',
@subject = 'HTML mail',
@body = @tableHTML,
@body_format = 'HTML' ;

-- Checking Mail Delivery status
-- Delivery Status can be viewed from column sent_status
-- Check all mails AND Clear histroy or allitems
select * from msdb.dbo.sysmail_allitems order by last_mod_date desc
-- DELETE from msdb.dbo.sysmail_allitems

-- Check sent mails and clear
select * from msdb.dbo.sysmail_sentitems order by last_mod_date desc
-- DELETE from msdb.dbo.sysmail_sentitems
-- Check failed mails
select * from msdb.dbo.sysmail_faileditems order by last_mod_date desc
-- If you want to clear run this script
-- DELETE from msdb.dbo.sysmail_faileditems
-- Checking Failed Mail Reason
-- Column Description returns the Failure reason
select * from msdb.dbo.sysmail_event_log where mailitem_id ='1507'
DELETE from msdb.dbo.sysmail_event_log

-- Monitoring Database Mail run msdb DB
SELECT * FROM msdb.dbo.sysmail_allitems where recipients='dsaffsdfds@yahoo.com'
--delete FROM msdb.dbo.sysmail_allitems where recipients='dsafsadf@yahoo.com'
SELECT * FROM msdb.dbo.sysmail_event_log
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo. sysmail_mailattachments
SELECT count(*) FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT count(*) FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems

-- Troubleshooting DB Mail --

/*
Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 72
Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery.
*/
/* A value of 0 indicates that Service Broker message delivery is not activated in the msdb database */
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
--- check members of the DatabaseMailUserRole
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole'
EXEC msdb.dbo.sysmail_help_principalprofile_sp
--- check status of Database Mail
EXECUTE msdb.dbo.sysmail_help_status_sp ;
---start Database Mail in a mail host database
EXECUTE msdb.dbo.sysmail_start_sp

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'
-- Kill all connection to msdb database and run:
-- stop the agent service, run the query, and then restart the service.
ALTER DATABASE msdb SET ENABLE_BROKER

-- ALTER DATABASE MSDB SET NEW_BROKER

-- You can set is_broker_enabled column value to 1 by executing the following command
ALTER DATABASE Employee SET NEW_BROKER

ALTER DATABASE Employee SET ENABLE_BROKER

ALTER DATABASE Employee SET ERROR_BROKER_CONVERSATIONS

ALTER DATABASE Employee SET TRUSTWORTHY ON

ALTER DATABASE Employee SET DISABLE_BROKER