Trigger Help

[mjwigham]

Hi Guys,

I am trying to create my first trigger.
I control 2 SQL servers.

I have a DB called Web with a table called REGUSERS.
I want to execute a Stored Procedure on another server, when a field is changed on any entry in the REGUSERS table.

Executing the SP is not a problem, i can do that.

What I do not know is how write the trigger.

When a field called UserActive in the REGUSERS table changes to "Active", I want to parse a few fields from the row that has changed, to variables, so i can use them to parse to the stored procedure.

Any help would be greatly appreciated.

Mark

[mikr0s]

CREATE TRIGGER trig_upd_REGUSERS
ON REGUSERS
FOR UPDATE --(or/and insert...)
AS

--declare temporary table holding values of records that satisfy
--UserActive = 'Active'
--Note:fld1, fld2,...point to definition of fields in REGUSERS table
DECLARE @tmp TABLE (ident INT IDENTITY,
fld1 [definition],
fld2 [definition]
.....[definition])

--catch all updated records having UserActive = 'Active'
INSERT INTO @tmp(fld1, fld2,....)
SELECT fld1, fld2, ....
FROM inserted
WHERE UserActive = 'Active'

--run through records
DECLARE @fld1 [definition], @fld2 [definition]....

DECLARE @i INT, @lim INT

SELECT @i = ISNULL(MIN(ident), 1), @lim = ISNULL(MAX(ident), 0)
FROM @tmp

WHILE @i <= @lim
BEGIN
--get values of current row
SELECT @fld1 = fld1, @fld2 = fld2, @.......= .....
FROM @tmp
WHERE ident = @i

--execute procedure for each set of values
EXEC server2..myproc @value1 = @fld1, @value2 = @fld2, @...= @....

--move to next record
SET @i = @i + 1

END

--HTH--

[mjwigham]

Thanks.

I will try it out, and let you know.

Mark

[mjwigham]

That worked great.

Thanks

[mjwigham]

I have another slight issue i need help with.

I need to put in an IF statement so that if the forename and surname from the REGUSERSTEMP table already exist in a table called Users on a separate DB called MAPS, not to run the SP, but to send an e-mail instead.

This is my trigger

CREATE TRIGGER [dbo].[Tg_Create_MAPS_User]
ON [dbo].[REGUSERSTEMP]
for UPDATE, INSERT
AS

DECLARE @tmp TABLE (ident INT IDENTITY,
REGUSERSID int,
EMAIL varchar(200),
FORENAME varchar (50),
SURNAME varchar(50),
USERPASSWORD varchar(50),
USERACTIVE varchar(20))

INSERT INTO @tmp(REGUSERSID, EMAIL, FORENAME, SURNAME, USERPASSWORD, USERACTIVE)
SELECT REGUSERSID, EMAIL, FORENAME, SURNAME, USERPASSWORD, USERACTIVE
FROM inserted
WHERE USERACTIVE = 'Active'

DECLARE @REGUSERSID int, @EMAILe varchar(200), @FORENAMEe varchar (50),
@SURNAMEe varchar(50), @USERPASSWORD varchar(50), @USERACTIVE varchar(20)

DECLARE @i INT, @lim INT

SELECT @i = ISNULL(MIN(ident), 1), @lim = ISNULL(MAX(ident), 0)
FROM @tmp

WHILE @i <= @lim

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT @REGUSERSID = REGUSERSID, @EMAILe = EMAIL, @FORENAMEe = FORENAME, @SURNAMEe = SURNAME,
@USERPASSWORD = USERPASSWORD, @USERACTIVE = USERACTIVE
FROM @tmp
WHERE ident = @i

-- Insert statements for trigger here
exec A0003FF4435C3.MAPS.dbo.sp_BackOffice_Users_UserDet ails_U
@ItemID = 0,
@UserName = @EMAILe,
@Forename = @FORENAMEe,
@Password = @USERPASSWORD,
@Surname = @SURNAMEe,
@Email = @EMAILe,
@UserShortCode = 'U-B',
@UserGroupID = '65',
@UserTypeID = '11',
@BusinessCentreID = '28',
@AccessToAllBusinessCentres = '0',
@SelectedProcCentresIDs = '28',
@DefaultProcCentreID = '28',
@AuthorityID = null,
@Disabled = '0',
@UserID = '1291'

SET @i = @i + 1

END

[mikr0s]

/* Possible changes/modification */

DECLARE @tmp TABLE (ident INT IDENTITY,
REGUSERSID int,
EMAIL varchar(200),
FORENAME varchar (50),
SURNAME varchar(50),
USERPASSWORD varchar(50),
USERACTIVE varchar(20),
the_action varchar(1)) --new added field holding action 'E'-mail or 'S'-tored procedure

--catch inserted (on Update or Insert) records that have 'Active'
--Left join is used for checking if a record already exist in other database..table
INSERT INTO @tmp(REGUSERSID, EMAIL, FORENAME, SURNAME, USERPASSWORD, USERACTIVE, the_action)
SELECT i.REGUSERSID, i.EMAIL, i.FORENAME, i.SURNAME, i.USERPASSWORD, i.USERACTIVE, CASE WHEN muser.SURNAME IS NULL THEN 'S' ELSE 'E' END
FROM inserted i
LEFT JOIN MAPS.dbo.Users muser ON i.FORENAME = muser.FORENAME AND i.SURNAME = muser.SURNAME
WHERE i.USERACTIVE = 'Active'

/*Note:
If there are different collations between databases, you could use :

ON i.FORENAME = muser.FORENAME COLLATE database_default AND i.SURNAME = muser.SURNAME COLLATE database_default

to avoid collation conflicts on code execution
*/

--declare @the_action nvarchar(1)
SELECT @REGUSERSID = REGUSERSID, @EMAILe = EMAIL, @FORENAMEe = FORENAME, @SURNAMEe = SURNAME,
@USERPASSWORD = USERPASSWORD, @USERACTIVE = USERACTIVE, @the_action = the_action
FROM @tmp
WHERE ident = @i



IF @the_action = 'S'
BEGIN
--execute procedure
exec A0003FF4435C3.MAPS.dbo.sp_BackOffice_Users_UserDet ails_U
@ItemID = 0,


END
ELSE --action will be E-mail
BEGIN
----send email
execute the_process_of_sending_email
END

--Note: You could trap any errors raised by calling the procedures using a statement like EXEC @myerror = procedure_name
IF @myerror <> 0
BEGIN
--error handling actions
END

--HTH--

[mjwigham]

I have made the ammendments that you have described, but when i update the table to start the trigger, it starts to create hundreds of users, but never commits the data.

Here is what i have

I have just altered the original SP action in the else clause whilst i am testing.

ALTER TRIGGER [dbo].[Tg_Create_MAPS_User]
ON [dbo].[REGUSERSTEMP]
for UPDATE
AS

DECLARE @tmp TABLE (ident INT IDENTITY,
REGUSERSID int,
EMAIL varchar(200),
FORENAME varchar (50),
SURNAME varchar(50),
USERPASSWORD varchar(50),
USERACTIVE varchar(20),
The_Action nvarchar(1))

INSERT INTO @tmp(REGUSERSID, EMAIL, FORENAME, SURNAME, USERPASSWORD, USERACTIVE, the_action)
SELECT i.REGUSERSID, i.EMAIL, i.FORENAME, i.SURNAME, i.USERPASSWORD, i.USERACTIVE, CASE WHEN muser.SURNAME IS NULL THEN 'S' ELSE 'E' END
FROM inserted i
LEFT JOIN MAPS.dbo.Users muser ON i.FORENAME = muser.FORENAME COLLATE database_default AND i.SURNAME = muser.SURNAME COLLATE database_default
WHERE i.USERACTIVE = 'Active'



DECLARE @REGUSERSID int, @EMAILe varchar(200), @FORENAMEe varchar (50),
@SURNAMEe varchar(50), @USERPASSWORD varchar(50), @USERACTIVE varchar(20), @the_action nvarchar(1)

DECLARE @i INT, @lim INT

SELECT @i = ISNULL(MIN(ident), 1), @lim = ISNULL(MAX(ident), 0)
FROM @tmp

WHILE @i <= @lim

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT @REGUSERSID = REGUSERSID, @EMAILe = EMAIL, @FORENAMEe = FORENAME, @SURNAMEe = SURNAME,
@USERPASSWORD = USERPASSWORD, @USERACTIVE = USERACTIVE, @the_action = the_action
FROM @tmp
WHERE ident = @i

IF @the_action = 'S'
BEGIN

-- Insert statements for trigger here
exec MAPS.dbo.sp_BackOffice_Users_UserDetails_U
@ItemID = 0,
@UserName = @EMAILe,
@Forename = @FORENAMEe,
@Password = @USERPASSWORD,
@Surname = @SURNAMEe,
@Email = @EMAILe,
@UserShortCode = 'U-B',
@UserGroupID = '65',
@UserTypeID = '11',
@BusinessCentreID = '28',
@AccessToAllBusinessCentres = '0',
@SelectedProcCentresIDs = '28',
@DefaultProcCentreID = '28',
-- @IFABranchID Int,
-- @BranchManager bit,
@AuthorityID = '11',
@Disabled = '0',
@UserID = '1291'

SET @i = @i + 1
END
ELSE --action will be E-mail
BEGIN
----send email
exec MAPS.dbo.sp_BackOffice_Users_UserDetails_U
@ItemID = 0,
@UserName = 'Else Query',
@Forename = @FORENAMEe,
@Password = @USERPASSWORD,
@Surname = @SURNAMEe,
@Email = @EMAILe,
@UserShortCode = 'U-B',
@UserGroupID = '65',
@UserTypeID = '11',
@BusinessCentreID = '28',
@AccessToAllBusinessCentres = '0',
@SelectedProcCentresIDs = '28',
@DefaultProcCentreID = '28',
-- @IFABranchID Int,
-- @BranchManager bit,
@AuthorityID = '11',
@Disabled = '0',
@UserID = '1291'
END
end

[mikr0s]

-- since you have the iterator @i variable in the IF block, it
-- will be updated to @i+1 only when @the_action = 'S'
--but @i should be updated on every pass

Place SET @i = @i + 1
out of the if end block of code

--begin IF block code
IF @the_action = 'S'
BEGIN

SET @i = @i + 1 <-> DELETE this

END
ELSE --action will be E-mail
BEGIN

END
--end of if block code--

SET @i = @i + 1 <-> put it here

end <-> end of WHILE loop

--HTH--