/** * * * Object:trigger [dbo]. [Trg_del] Script date:01/01/2016 12:58:28 * * * * **/
SETAnsi_nulls on
GO
SETQuoted_identifier on
GO
CREATE TRIGGER [dbo].[Trg_del] on [dbo].[Common_header]INSTEAD of DELETE
as
BEGIN
DECLARE @cou INT
DECLARE @headerid uniqueidentifier
SELECT @cou = COUNT(*)
fromdeleted; SELECT @headerid =ID fromdeletedIF(@cou > 1 )
RAISERROR('data is not allowed to be deleted!', -,1)
ELSE
DELETE from [Common_header]
WHEREId= @headerid
END
GO
CREATE TABLE [dbo].[Common_header]
(
[ID] [uniqueidentifier] not NULL,
[Subject] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[Applicantad] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[Applicantname] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[CountryCode] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[CountryName] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[Companycode] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[CompanyName] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[Divisioncode] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[Divisionname] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[Lbucode] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[Lbuname] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[CostCenter] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[Email] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL,
[CreatedDate] [datetime] NULL,
[Queueid] [uniqueidentifier] NULL,
[Status] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL
) on [PRIMARY]
GO
ALTER TABLE [dbo].[Common_header] ADD CONSTRAINT [Pk_common_header] PRIMARY KEY CLUSTERED([ID]) on [PRIMARY]
GO
CREATE nonclustered INDEX [nonclusteredindex-20150603-113054] on [dbo].[Common_header]([Queueid]) on [PRIMARY]
GO
T-SQL triggers, which limit the deletion of only one piece of data at a time