‎10-05-2021 03:04 PM
‎11-02-2021 02:21 PM
‎10-28-2021 11:18 AM
‎10-28-2021 10:19 AM
‎10-28-2021 11:00 AM
‎10-28-2021 11:15 AM
prighi61 wrote:
Hi Alexey T, you can try this:
CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
DECLARE @Displayname nvarchar(301), @deletedUserdomain nvarchar(150), @deletedUsername nvarchar(150), @insertedUserdomain nvarchar(150), @insertedUsername nvarchar(150);
SELECT @deletedUserdomain = Userdomain, @deletedUsername = Username from deleted;
SELECT @insertedUserdomain = Userdomain, @insertedUsername = Username from inserted;
if @insertedUserdomain is not null and @insertedUsername is not null and ( @insertedUserdomain <> @deletedUserdomain or @insertedUsername <> @deletedUsername )
SELECT @Displayname = Displayname from web40repADusernodisplayname, inserted where web40repADusernodisplayname.Userdomain = inserted.Userdomain and web40repADusernodisplayname.Username = inserted.Username;
Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%'
END
‎10-29-2021 02:08 PM
prighi61 wrote:prighi61 wrote:
Hi Alexey T, you can try this:
CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
DECLARE @Displayname nvarchar(301), @deletedUserdomain nvarchar(150), @deletedUsername nvarchar(150), @insertedUserdomain nvarchar(150), @insertedUsername nvarchar(150);
SELECT @deletedUserdomain = Userdomain, @deletedUsername = Username from deleted;
SELECT @insertedUserdomain = Userdomain, @insertedUsername = Username from inserted;
if @insertedUserdomain is not null and @insertedUsername is not null and ( @insertedUserdomain <> @deletedUserdomain or @insertedUsername <> @deletedUsername )
SELECT @Displayname = Displayname from web40repADusernodisplayname, inserted where web40repADusernodisplayname.Userdomain = inserted.Userdomain and web40repADusernodisplayname.Username = inserted.Username;
Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%'
END
Please replace underlined statement with:
SELECT @Displayname = Displayname from tblADusers, inserted where tblADusers.Userdomain = inserted.Userdomain and tblADusers.Username = inserted.Username;
‎10-29-2021 02:33 PM
‎11-01-2021 08:40 AM
prighi61 wrote:
...
However, instead of using a union you can check whether @Displayname is null or empty and set it via another select.
CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
DECLARE @Displayname nvarchar(301), @deletedUserdomain nvarchar(150), @deletedUsername nvarchar(150), @insertedUserdomain nvarchar(150), @insertedUsername nvarchar(150);
SELECT @deletedUserdomain = Userdomain, @deletedUsername = Username from deleted;
SELECT @insertedUserdomain = Userdomain, @insertedUsername = Username from inserted;
if @insertedUserdomain is not null and @insertedUsername is not null and ( @insertedUserdomain <> @deletedUserdomain or @insertedUsername <> @deletedUsername )
BEGIN
SELECT @Displayname = Displayname from tblADusers, inserted where tblADusers.Userdomain = inserted.Userdomain and tblADusers.Username = inserted.Username;
if @Displayname is null or @Displayname = ''
SELECT @Displayname = Userdomain + '\' + Username from inserted;
Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%'
END
‎11-02-2021 09:22 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now