November 25, 2010 at 8:49 pm
Hi i have two tables
1.ROLE table
CREATE TABLE [dbo].[Role](
[RoleID] [smallint] NOT NULL,
[RoleName] [varchar](50) NOT NULL,
[IsDeleted] [bit] NOT NULL,
[IsWsAdmin] [bit] NOT NULL,
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
2. ROLECONSUMER Table
CREATE TABLE [dbo].[RoleConsumer](
[RoleConsumerID] [int] NOT NULL,
[RoleName] [varchar](50) NOT NULL,
[ConsumerID] [int] NOT NULL,
[IsActive] [bit] NOT NULL,
[IsDefault] [bit] NOT NULL,
CONSTRAINT [PK_RoleConsumer] PRIMARY KEY CLUSTERED
(
[RoleConsumerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The sample data for Role table is
RoleIDRoleName IsDeletedIsWsAdmin
1WsAdmin 0 1
2Admin 0 0
3Helpdesk 0 0
4 Support 0 0
The sample data for RoleConsumer table is
RoleConsumerIDRoleName ConsumerIDIsActiveIsDefault
1 Admin 1 1 1 --these roles are copy from the Role table
2 Helpdesk 1 1 1 "
3 Dynamic Role 1 1 0 --these r created while consumer iscreated
4 Admin 2 1 1
5 Support 2 1 1
6 Desksupport 2 1 0
7 Admin 3 1 1
I want a trigger such that if i give IsDeleted = 1 in Role Table to any role, that role in ROLECONSUMER table become inactive
For example
in Role table For Admin role i put IsDeleted = 1 then all the admin role in RoleConsumer table become IsActive = 0
Thank you in advance
With regards DD
November 26, 2010 at 1:49 am
This was removed by the editor as SPAM
November 26, 2010 at 2:34 am
This was removed by the editor as SPAM
November 26, 2010 at 3:10 am
you can have something like this
Create trigger trg_update_Role_RoleConsumer
On [Role]
For update
As
declare @IsDeleted bit
declare @RoleName varchar(50)
select @IsDeleted = inserted.[IsDeleted] from inserted
select @RoleName = inserted.[RoleName] from inserted
if @IsDeleted = 1
begin
update [RoleConsumer] set [IsActive] = 0 where [RoleName] = @RoleName
end
November 26, 2010 at 3:11 am
i am sorry, stewartc-708166 gave a correct solution than what i wrote
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply