January 12, 2011 at 1:11 pm
CREATE TRIGGER LastLoginDate ON dbo.users AFTER update
--//ColumnName
DECLARE @Last_Login_Date datetime
--//DataValue
SET @Last_Login_Date = (SELECT Last_Login_Date FROM inserted)
--//Condition
IF @last_login_date from dbo.users where (user_id = 'bbsupport');
--//True?
BEGIN
DECLARE @msg varchar(500)
--// SET THE Message sent, Recipient list and subject using which SQLmail profile.
SET @msg = 'BBsupport "' + @Last_Login_Date + '" Support Accounts Authenticated' + '.'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'MyEmail Address', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'Administrator'
END
I get errors:
Msg 156, Level 15, State 1, Procedure LastLoginDate, Line 4
Incorrect syntax near the keyword 'DECLARE'.
Msg 156, Level 15, State 1, Procedure LastLoginDate, Line 11
Incorrect syntax near the keyword 'from'.
Suggestions?
January 12, 2011 at 1:15 pm
You're missing "AS" between the create statement and the first line of code in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 1:17 pm
Also, I can't tell what this is meant to do: IF @last_login_date from dbo.users where (user_id = 'bbsupport');
Can't suggest a handling for it, since I don't know the desired end result.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 1:20 pm
The desired result is from any change to the last login date for that user, I want an email. Basically I'll know any time that user account logs in. (update, to the Last_Login_Date where user = bbsupport)
January 12, 2011 at 1:32 pm
Wouldn't it be easier to query the inserted dataset to see if the user name is the one you want, and then procede from there?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 1:34 pm
Like this???
IF @last_login_date from inserted where (user_id = 'bbsupport');
January 12, 2011 at 1:38 pm
CREATE TRIGGER LastLoginDate ON dbo.users AFTER update
AS -- first error fixed here
--//ColumnName
DECLARE @Last_Login_Date datetime, @msg varchar(500);
IF EXISTS
(SELECT 1
FROM inserted
WHERE user_id = 'bbsupport')
BEGIN
--//DataValue
SET @Last_Login_Date = (SELECT Last_Login_Date FROM inserted);
--// SET THE Message sent, Recipient list and subject using which SQLmail profile.
SET @msg = 'BBsupport "' + @Last_Login_Date + '" Support Accounts Authenticated.';
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'MyEmail Address',
@body= @msg, @subject = 'SQL Server Trigger Mail',
@profile_name = 'Administrator';
END;
Something like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 3:20 pm
Your modifications do allow a trigger create. But I can't prove it works. I can login as that user, the last_login_date value does change. But I'm not triggering an email. The messaging portion of this is from another trigger that works fine.
I'm not sure I understand:
IF EXISTS
(SELECT 1
FROM inserted
WHERE user_id = 'bbsupport')
--//above would be if that account exists in 'inserted'. But where's the trigger of data change to write the value there?
BEGIN
--//DataValue
SET @Last_Login_Date = (SELECT Last_Login_Date FROM inserted);
--//I don't understand setting the value to be the same in both locations.
January 12, 2011 at 4:08 pm
Try this:
change
IF EXISTS
(SELECT 1
FROM inserted
WHERE user_id = 'bbsupport')
to
IF NOT EXISTS
(SELECT 1
FROM inserted i
INNER JOIN deleted d ON i.Last_Login_Date = d.Last_Login_Date)
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
January 12, 2011 at 4:17 pm
GSquared (1/12/2011)
CREATE TRIGGER LastLoginDate ON dbo.users AFTER updateAS -- first error fixed here
--//ColumnName
DECLARE @Last_Login_Date datetime, @msg varchar(500);
IF EXISTS
(SELECT 1
FROM inserted
WHERE user_id = 'bbsupport')
BEGIN
--//DataValue
SET @Last_Login_Date = (SELECT Last_Login_Date FROM inserted);
--// SET THE Message sent, Recipient list and subject using which SQLmail profile.
SET @msg = 'BBsupport "' + @Last_Login_Date + '" Support Accounts Authenticated.';
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'MyEmail Address',
@body= @msg, @subject = 'SQL Server Trigger Mail',
@profile_name = 'Administrator';
END;
Something like that.
Hi GSquared - it looks like you may have overlooked something here by accident - that subquery
SET @Last_Login_Date = (SELECT Last_Login_Date FROM inserted);
is not safe - it could return multiple rows and cause an error.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 12, 2011 at 4:21 pm
Although this will cause an error if more than one row is updated. You could change
SET @Last_Login_Date = (SELECT Last_Login_Date FROM inserted);
to
SET @Last_Login_Date = (SELECT TOP 1 Last_Login_Date FROM inserted);
For a temp fix. But it would help if we knew the DDL of the users table.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
January 13, 2011 at 7:09 am
Yes, if the update includes multiple rows, the trigger will have a problem. I've never yet seen a login process that allowed for a multi-row log entry, since every one of them is pretty much intended to deal with one person logging in. On that assumption, over-engineering seems unnecessary.
I'd have to see the code that's causing the trigger to fire to be able to tell you what's going on here. The trigger will fire when the table has an update statement run on it. How is the update issued? What columns does it modify?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 10:34 am
Is there a way to test the trigger? The last_login_date value for user bbsupport gets updated when the user logs in via the application. I've logged in as that user but don't get an email. (messaging tests work good though) I can't query "Last_Login_Date FROM inserted"
-Steve
January 13, 2011 at 4:31 pm
I think your trigger is erroring out. Run this to update GSquared code:
ALTER TRIGGER LastLoginDate ON dbo.users AFTER update
AS -- first error fixed here
--//ColumnName
DECLARE @Last_Login_Date datetime, @msg varchar(500);
IF EXISTS
(SELECT 1
FROM inserted
WHERE user_id = 'bbsupport')
BEGIN
--//DataValue
SET @Last_Login_Date = (SELECT Last_Login_Date FROM inserted);
--// SET THE Message sent, Recipient list and subject using which SQLmail profile.
SET @msg = 'BBsupport "' + CAST(@Last_Login_Date AS VARCHAR(100)) + '" Support Accounts Authenticated.';
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'MyEmail Address',
@body= @msg, @subject = 'SQL Server Trigger Mail',
@profile_name = 'Administrator';
END;
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
January 13, 2011 at 4:40 pm
I guess I should be clearer. The trigger is breaking when concatenating the msg with the @Last_Login_Date, so I wrapped it in a CAST. You didn't see that error because you tested from the application and not SSMS.
I can't query "Last_Login_Date FROM inserted"
inserted is a virtual table that only exists in the trigger. From the BOL:
deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:
SELECT *
FROM deleted
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply