SQLAzureMW does not include triggers for my tables

Jan 15, 2010 at 12:54 PM
Edited Jan 15, 2010 at 12:57 PM

Hi!

Using this great tool, I've seen that my triggers are not included in the generated SQL, without returning any messages.

If I export the trigger from my local db using SQL Management Studio, copy it, and paste it into SQL Azure, works just fine.

Am I missing something?

Here it is one of the triggers that's not beeing exported - and all of them are pretty similar, so you might find something out:


/****** Object: Trigger [dbo].[DiasReportesInsertMT] Script Date: 01/15/2010 11:50:02 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[DiasReportesInsertMT]
ON [dbo].[DiasReportes]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE DiasReportes
SET TenantID = (SELECT Id FROM Tenants WHERE UsuarioBD = CURRENT_USER)
WHERE Id = (SELECT Id FROM INSERTED) AND (select [dbo].[usuarioEsAdministrador]((CURRENT_USER))) = 0
END
GO

And here is the function 'usuarioEsAdministrador' which is included in the sql

/****** Object:  UserDefinedFunction [dbo].[usuarioEsAdministrador]    Script Date: 01/15/2010 11:51:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[usuarioEsAdministrador]
(
@usuario nvarchar(50)
)
RETURNS bit
AS
BEGIN
if (select count(*)
from sys.database_role_members miembros
inner join sys.database_principals principals on miembros.member_principal_id = principals.principal_id
inner join sys.database_principals roles on miembros.role_principal_id = roles.principal_id
where roles.name = 'Administradores' and principals.name = @usuario) >0
begin
return 1;
end
return 0;
END
GO

Thanks for any suggestions!

Coordinator
Jan 15, 2010 at 5:16 PM

Hi!

You did not miss anything, I did.  I enabled the switch to enable you to (or not to) script table triggers.  By default, it will script table triggers not.  Please check out v3.1.3 and see if it works for you.

Thanks for catching this for me.

George

Jan 15, 2010 at 8:08 PM

Wow, that was extremely fast!

thank you really much, George!